Graphing the distribution of ages, Dateadd function

[AlineVincent]

Hello,
i have a table that has a field with the birthdate of all members. I want to know how many members are between 15 and 40 years old, how many are between 40 and 50, between 50 and 70.
I also would like to put these informations into a graph in order to see the distribution of the ages.
Is there an immediate way to graph these distributions or do i need to create a query which contains all the data to graph?
I tried to write a criteria for a query using the dateadd function in this way :
BETWEEN DATE() AND DATEADD("yyyy",-35,DATE())
but it seems impossible to make it work. I tried to put ";" instead of "," but it didn't work.
I would really appreciate any help! Thanks

 

[Chris RR]

I'd take what, to me, is the easy way out, and build a query.

First, I'd create a field in that query that is the person's age, in years. That should simply be subtracting the birthdate from today's date and formatting the result.

Once that calculation is working smoothly, I'd put the same calc in a new calculated field, as part of some "iif" logic. This field would build "age group" ("A" = 0 to 14, "B" = 15 to 40, and so on...)

Then you can base any reporting or graphing on the age group in your query, and it should be pretty easy from there.

 

[aqif]

Hi

I think this query solve ur problem.

SELECT IIf(IsNull((Date()-[ptdob])/365.25),Null,IIf((Date()-[ptdob])/365.25<20,"<20",IIf((Date()-[ptdob])/365.25<30,"20-29",IIf((Date()-[ptdob])/365.25<40,"30-39",IIf((Date()- [ptdob])/365.25<50,"40-49",IIf((Date()-[ptdob])/365.25<60,"50-59",IIf((Date()-[ptdob])/365.25<70,"60-69",IIf((Date()-[ptdob])/365.25<80,"70-79",IIf((Date()-[ptdob])/365 .25<90,"80-89","90+"))))))))) AS agegrp, Count(TblPrimaryDiagnosis.PtID) AS Count
FROM TblPatients INNER JOIN TblPrimaryDiagnosis ON TblPatients.PtID = TblPrimaryDiagnosis.PtID
GROUP BY IIf(IsNull((Date()-[ptdob])/365.25),Null,IIf((Date()-[ptdob])/365.25<20,"<20",IIf((Date()-[ptdob])/365.25<30,"20-29",IIf((Date()-[ptdob])/365.25<40,"30-39",IIf((Date()- [ptdob])/365.25<50,"40-49",IIf((Date()-[ptdob])/365.25<60,"50-59",IIf((Date()-[ptdob])/365.25<70,"60-69",IIf((Date()-[ptdob])/365.25<80,"70-79",IIf((Date()-[ptdob])/365 .25<90,"80-89","90+")))))))));

I hope this query is what u r looking for.

Cheers!
Aqif