If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average Age
I'm doing end of term reports for pupils in a school whereby each report
needs to show the age of the pupil on a given date in YEARS and MONTHS (not a fraction of the year). In other words, 10 yrs and 3 months, (not 10.25). I've managed to use a function called Diff2Dates Author: © Copyright 2001 Pacific Database Pty Limited Graham R Seach MCP MVP to display the pupils age in years and months fine, but I ALSO need to show the Average Age of the Class in Years and Months. Simply adding up the results of the Function above and dividing by the number of children did not work accurately, nor did using AVG as the result was a fraction of the year and not the exact average of months. Does anyone have a function for calculating the Average Age in Years and Months, please? Thanks Laura |
#2
|
|||
|
|||
Average Age
Why not average and then apply the function called Diff2Dates?
-- Build a little, test a little. "Laura" wrote: I'm doing end of term reports for pupils in a school whereby each report needs to show the age of the pupil on a given date in YEARS and MONTHS (not a fraction of the year). In other words, 10 yrs and 3 months, (not 10.25). I've managed to use a function called Diff2Dates Author: © Copyright 2001 Pacific Database Pty Limited Graham R Seach MCP MVP to display the pupils age in years and months fine, but I ALSO need to show the Average Age of the Class in Years and Months. Simply adding up the results of the Function above and dividing by the number of children did not work accurately, nor did using AVG as the result was a fraction of the year and not the exact average of months. Does anyone have a function for calculating the Average Age in Years and Months, please? Thanks Laura . |
#3
|
|||
|
|||
Average Age
Laura:
You can get the total number of months of each pupil's Age with: DateDiff("m",[DoB],Date())+IIf(Day([DoB])=Day(Date()),1,0) So in a query you can average that with: AvgAgeInMonths: AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0)) You can of course substitute a literal date for the Date() function if you want the age on a specific date rather than the current date. You can convert that to years and month with a combination of integer division and the Mod operator. You can do it in the query by repeating the expression: AvgAge: AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0))\12 & " yrs and " & AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0)) Mod 12 & " months" or you do the same in a footer of a report which lists all the pupils, in an unbound text box, using the same expression as the ControlSource: =Avg(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0))\12 & " yrs and " & Avg(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0)) Mod 12 & " months" Ken Sheridan Stafford, England Laura wrote: I'm doing end of term reports for pupils in a school whereby each report needs to show the age of the pupil on a given date in YEARS and MONTHS (not a fraction of the year). In other words, 10 yrs and 3 months, (not 10.25). I've managed to use a function called Diff2Dates Author: © Copyright 2001 Pacific Database Pty Limited Graham R Seach MCP MVP to display the pupils age in years and months fine, but I ALSO need to show the Average Age of the Class in Years and Months. Simply adding up the results of the Function above and dividing by the number of children did not work accurately, nor did using AVG as the result was a fraction of the year and not the exact average of months. Does anyone have a function for calculating the Average Age in Years and Months, please? Thanks Laura -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#4
|
|||
|
|||
Average Age
Correction. First expression should have been:
DateDiff("m",[DoB],Date())+IIf(Day([DoB])Day(Date()),1,0) KenSheridan Stafford, England -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#5
|
|||
|
|||
Average Age
Ken, thanks for replying so quickly, it's so helpful.
Your formula works - thank you so much. I got slightly confused - did you mean a minus or plus sign before the IIF? I'm not sure of the significance. _________________________________________________ DateDiff("m",[DoB],Date())+IIf(Day([DoB])Day(Date()),1,0) AvgAgeInMonths: AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0)) _________________________________________________ Either seems to work. I used it in the Query Builder window and can program it to "Enter Date" so that the school can use it for each of the 3 terms of the year to then mailmerge into the School Reports. Many thanks again. Laura Wimbledon London UK "KenSheridan via AccessMonster.com" u51882@uwe wrote Correction. First expression should have been: DateDiff("m",[DoB],Date())+IIf(Day([DoB])Day(Date()),1,0) "KenSheridan via AccessMonster.com" u51882@uwe wrote in message news:a389039a04f4e@uwe... Laura: You can get the total number of months of each pupil's Age with: DateDiff("m",[DoB],Date())+IIf(Day([DoB])=Day(Date()),1,0) So in a query you can average that with: AvgAgeInMonths: AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0)) You can of course substitute a literal date for the Date() function if you want the age on a specific date rather than the current date. You can convert that to years and month with a combination of integer division and the Mod operator. You can do it in the query by repeating the expression: AvgAge: AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0))\12 & " yrs and " & AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0)) Mod 12 & " months" or you do the same in a footer of a report which lists all the pupils, in an unbound text box, using the same expression as the ControlSource: =Avg(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0))\12 & " yrs and " & Avg(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0)) Mod 12 & " months" Ken Sheridan Stafford, England Laura wrote: I'm doing end of term reports for pupils in a school whereby each report needs to show the age of the pupil on a given date in YEARS and MONTHS (not a fraction of the year). In other words, 10 yrs and 3 months, (not 10.25). I've managed to use a function called Diff2Dates Author: © Copyright 2001 Pacific Database Pty Limited Graham R Seach MCP MVP to display the pupils age in years and months fine, but I ALSO need to show the Average Age of the Class in Years and Months. Simply adding up the results of the Function above and dividing by the number of children did not work accurately, nor did using AVG as the result was a fraction of the year and not the exact average of months. Does anyone have a function for calculating the Average Age in Years and Months, please? Thanks Laura -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#6
|
|||
|
|||
Average Age
Laura:
Sorry, it should be a minus sign throughout. Put it down to how depressingly high the result is when I apply the expression to own date of birth! The way it works is that the DateDiff function gets the straight difference in months between the two dates regardless of the day of the month in eaither, so if the day of the month of the date of birth is after the day of the month of the current date one month has to be subtracted so that the result is whole months only. To see the difference enter the following in the debug window: ? DateDiff("m",#2000-10-11#,#2010-02-12#)-IIf(Day(#2000-11-12#)Day(#2010-02- 12#),1,0) and then enter: ? DateDiff("m",#2000-10-13#,#2010-02-12#)-IIf(Day(#2000-10-13#)Day(#2010-02- 12#),1,0) Now where did I leave that Zimmer frame? Ken Sheridan Stafford, England Laura wrote: Ken, thanks for replying so quickly, it's so helpful. Your formula works - thank you so much. I got slightly confused - did you mean a minus or plus sign before the IIF? I'm not sure of the significance. _______________________________________________ __ DateDiff("m",[DoB],Date())+IIf(Day([DoB])Day(Date()),1,0) AvgAgeInMonths: AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0)) _______________________________________________ __ Either seems to work. I used it in the Query Builder window and can program it to "Enter Date" so that the school can use it for each of the 3 terms of the year to then mailmerge into the School Reports. Many thanks again. Laura Wimbledon London UK "KenSheridan via AccessMonster.com" u51882@uwe wrote Correction. First expression should have been: DateDiff("m",[DoB],Date())+IIf(Day([DoB])Day(Date()),1,0) Laura: [quoted text clipped - 56 lines] Thanks Laura -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#7
|
|||
|
|||
Average Age
Thanks for explaining, Ken - I know the feeling when applying to own
birthdate I think the same principle applies if you're working out the age of a person in terms of years.. you have to subtract a year, or something, or it works out that you're a year older than you are and that's even worse! Laura London "KenSheridan via AccessMonster.com" u51882@uwe wrote in message news:a38a3dca882ac@uwe... Laura: Sorry, it should be a minus sign throughout. Put it down to how depressingly high the result is when I apply the expression to own date of birth! The way it works is that the DateDiff function gets the straight difference in months between the two dates regardless of the day of the month in eaither, so if the day of the month of the date of birth is after the day of the month of the current date one month has to be subtracted so that the result is whole months only. To see the difference enter the following in the debug window: ? DateDiff("m",#2000-10-11#,#2010-02-12#)-IIf(Day(#2000-11-12#)Day(#2010-02- 12#),1,0) and then enter: ? DateDiff("m",#2000-10-13#,#2010-02-12#)-IIf(Day(#2000-10-13#)Day(#2010-02- 12#),1,0) Now where did I leave that Zimmer frame? Ken Sheridan Stafford, England Laura wrote: Ken, thanks for replying so quickly, it's so helpful. Your formula works - thank you so much. I got slightly confused - did you mean a minus or plus sign before the IIF? I'm not sure of the significance. ________________________________________________ _ DateDiff("m",[DoB],Date())+IIf(Day([DoB])Day(Date()),1,0) AvgAgeInMonths: AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)Day(Date()),1,0)) ________________________________________________ _ Either seems to work. I used it in the Query Builder window and can program it to "Enter Date" so that the school can use it for each of the 3 terms of the year to then mailmerge into the School Reports. Many thanks again. Laura Wimbledon London UK "KenSheridan via AccessMonster.com" u51882@uwe wrote Correction. First expression should have been: DateDiff("m",[DoB],Date())+IIf(Day([DoB])Day(Date()),1,0) Laura: [quoted text clipped - 56 lines] Thanks Laura -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#8
|
|||
|
|||
Average Age
Laura:
One other thing worth mentioning is that when you have a parameter in a query to enter a date time value it’s a good idea to declare the parameter in the query. This then avoids any possibility of a date being entered in short date format, e.g. 14/2/2010, being misinterpreted as an arithmetical expression. If this does happen it wouldn't raise an error as Access implements the date/time data type as a 64 bit floating point number, the integer part representing the days and the fractional part the times of day, so it would be interpreted as the date time value which the number represents. 14/2/2010 as an arithmetical expression results in a number which represents a date time value of 30 December 1899 00:05:01. This is because 30 December 1899 is 'day zero' in Access. You can see this by entering the following in the debug window: ? Format(CDate(14/2/2010),"dd mmmm yyyy hh:nn:ss") Parameters can be declared in query design view by selecting Parameters from the Query menu (or whatever the equivalent is in Access 2007), or in SQL view by adding a line to the beginning of the query. So for a parameter [Enter Date] it would be: PARAMETERS [Enter Date] DateTime; SELECT etc You can then be assured that however the user enters the date, provided it is a legitimate date value, it will always be interpreted correctly. Ken Sheridan Stafford, England Laura wrote: Thanks for explaining, Ken - I know the feeling when applying to own birthdate I think the same principle applies if you're working out the age of a person in terms of years.. you have to subtract a year, or something, or it works out that you're a year older than you are and that's even worse! Laura London Laura: [quoted text clipped - 59 lines] Thanks Laura -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#9
|
|||
|
|||
Average Age
Ken, you're a star - thank you so much.=20
I find that nearly every single time I need to use "date" it's different = from the previous time and therefore the coding is different too. I = think one could write a whole book just on "dates". I keep saving = valuable notes such as yours and appreciate your added input. Thank you = very much. Laura London "KenSheridan via AccessMonster.com" u51882@uwe wrote in message news:a3a1d70f521c6@uwe... Laura: One other thing worth mentioning is that when you have a parameter in a query to enter a date time value it's a good idea to declare the parameter in the query. This then avoids any possibility of a date being entered in short date format, e.g. 14/2/2010, being misinterpreted as an arithmetical expression. If this does happen it wouldn't raise an error as Access implements the date/time data type as a 64 bit floating point number, the integer part representing the days and the fractional part the times of day, so it would be interpreted as the date time value which the number represents. 14/2/2010 as an arithmetical expression results in a number which represents a date time value of 30 December 1899 00:05:01. This is because 30 December 1899 is 'day zero' in Access. You can see this by entering the following in the debug window: ? Format(CDate(14/2/2010),"dd mmmm yyyy hh:nn:ss") Parameters can be declared in query design view by selecting Parameters from the Query menu (or whatever the equivalent is in Access 2007), or in SQL view by adding a line to the beginning of the query. So for a parameter [Enter Date] it would be: PARAMETERS [Enter Date] DateTime; SELECT etc You can then be assured that however the user enters the date, provided it is a legitimate date value, it will always be interpreted correctly. Ken Sheridan Stafford, England Laura wrote: Thanks for explaining, Ken - I know the feeling when applying to own birthdate I think the same principle applies if you're working out the age of a person in terms of years.. you have to subtract a year, or something, or it works out that you're a year older than you are and that's even worse! Laura London Laura: [quoted text clipped - 59 lines] Thanks Laura -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
Thread Tools | |
Display Modes | |
|
|