Counting Dates of Birth...

[SherryLynn]

Ok, I am still working on this report. I have a section where Age Groups are counted for individuals.

For example:
We have 10 clients whose ages (according to date of birth) range from say 15 to 35 years. The age groups would be say; 1-15, 16-20, 21-30, 31-35 years. Sometimes we don't have a date of birth for the person and so the field is left blank. (However, initially we thought we would use a default date of 12/31/99 - which I don't think is a good idea - but maybe someone has a thought on this). One of the categories is "Unknown" when we don't have a date of birth. How can I count those on a report?

The way we are currently counting the fields with dates of birth is through a query. The query has groups and counts between the minimum and maximum value of the age (the ages are calculated on another query). Once that is calculated, I need to add a total of all the individuals (i.e. if there were 10 clients, then the total should be 10). I really could use a brilliant solution here. I'm not very experienced and I am sure there is an easy was to do this, but I can't think of it. Thanks for any help!

Sherry

 

[JoeMiller]

You could use a Crosstab query to accomplish this. What you would need is a query to put the person in a category (IE: Unknown, 1 to 15, etc...). Then in the crosstab query you set the category to the column heading and count the number of people in that heading. These can then be referred to in your report.

HTH Joe Miller
joe.miller@flotech.net

 

[SherryLynn]

Hi Joe,

How would I count the "null" values though for those who we don't have the dates of birth for?

Sherry

 

[MichaelRed]

Just lending a thought. If you calculate an AGE field in a query, then any DatOfBirth Which is 'empty' could contain "0". These Individul's ages would calculate to be >>>>>>> 35 (or any believeable Value). When You do the grouping, just 'rename' the group > 100 Years as "Unknown".

I can't quite 'compute' the crosstab query, as I do not know what you would put in for the column headings. The generic aggregate query qith count does all I can see which is useful:

the Table:

ContributorID ContLastName ContFirstName DOB
1 Doe John 11/25/90
2 Smith Roberg 10/23/75
3 Mary O'Conner 8/21/85
4 Karen Blessing 12:00:00 AM
5 Jeane Knight 7/22/70
6 Budd Billy 9/30/80

Not that Karen Blessing has 'no' DOB.

The AgeQuety SQL:
SELECT tblcontribDOB.ContLastName, tblcontribDOB.ContFirstName, tblcontribDOB.DOB, DateDiff(&quot;yyyy&quot;,[DOB],Now()) AS Age, IIf([Age]>0 And [Age]<16,&quot;1-15&quot;,IIf([Age]>15 And [Age]<21,&quot;16-20&quot;,IIf([Age]>20 And [Age]<26,&quot;21-25&quot;,IIf([Age]>25 And [Age]<31,&quot;25-30&quot;,IIf([Age]>30 And [Age]<36,&quot;30-35&quot;,&quot;UnKnown&quot)))) AS AgeGrp
FROM tblcontribDOB;


The AgeQuery Results:
ContLastName ContFirstName DOB Age Grp
Doe John 11/25/9 11 1-15
Smith Roberg 10/23/75 26 25-30
Mary O'Conner 8/21/85 16 16-20
Karen Blessing 12:00:00 AM 102 Unknown
Jeane Knight 7/22/70 31 30-35
Budd Billy 9/30/80 21 21-25

The Group/Count query SQL:
SELECT qryContribAge.AgeGrp, Count(qryContribAge.AgeGrp) AS CountOfAgeGrp
FROM qryContribAge
GROUP BY qryContribAge.AgeGrp;



The Group/CountQuery Results:
AgeGrp CountOfAgeGrp
1-15 1
16-20 1
21-25 1
25-30 1
30-35 1
UnKnown 1 MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[JoeMiller]

I have a small sample db that I can send you to illustrate MichaelRed's example. When I started to do this I realized a crosstab query was not a good idea, and when I came back and saw Michael suggested something similar to what I was going to suggest next! :-( Mine is a little bit different though, the method he used to calculate age can error if the person's birthday has not happened yet in the current year (my method accounts for that). And I used the Switch function instead of nested iif's. They are a little easier to work with.

If you'd like to see it, let me know and I'll ship it off to you. Joe Miller
joe.miller@flotech.net

 

[SherryLynn]

Hello,

Thanks Michael and Joe. Joe, please do send me the sample - I would appreciate it. Will what you have worked out work for blank fields? My email is yanch@escape.ca

Thanks again,
Sherry

 

[JoeMiller]

My db will account for blank fields, it is on the way to you now.

Joe Miller
joe.miller@flotech.net