Average Query

[cdg15]

Please can someone help.

How do I calculate the average date of birth in Access.

EG This gives me DOB and Age in years, how do I get the Average Age of ALL users?:


SELECT Pupil.DOB, DateDiff('yyyy',[DOB],Now()) AS Age
FROM Pupil;

Many Thanks

C

 

[dhookom]

DateDiff('yyyy',[DOB],Now()) is not and accurate age calculation. You can find accurate expressions at

http://www.mvps.org/access/datetime/date0001.htm.

You can then Average the result of the calculations.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cdg15]

Thanks for your comments. I will look at the link you provided. However, regardless of accuracy how do I write the SQL to average the age column??

Thanks

 

[PHV]

SELECT Avg(your age calculation here) As AverageAge
FROM Pupil

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cdg15]

Ok this is what I have:

SELECT Pupil.DOB, DateDiff('yyyy',[DOB],Now()) AS Age, Avg([Age]) as Avg_Age
FROM Pupil
group by Pupil.DOB, Age, Avg_Age;


On executing it asks me to enter parameter, I justy want to see the average of all their ages?

Thanks in advance

 

[PHV]

You may try something like this:
SELECT Pupil.DOB, DateDiff('yyyy',[DOB],Now()) AS Age, (SELECT Avg(DateDiff('yyyy',[DOB],Now())) FROM Pupil) AS Avg_Age
FROM Pupil

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cdg15]

Thank you PHV

That is exactly what I wanted - I can see where I went wrong now!



C

 

[dhookom]

Apparently you don't care if your age calculation is error-prone.

DateDiff('yyyy',[DOB],Now())

Consider someone born 12/31/2005. This baby would be one year old on the next day. A baby born 1/1/2005 would also be one year old on 12/31/2006. How could a baby 1 day old be the same age as a baby 729 days old?

Maybe in your case, this will even out in July of a year.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]