Count records between dates

[Baldlegal]

I have a db containing contacts with a birth date field (call it BDate). I want to count the number of contacts falling within given age ranges. For example, the number of contacts between ages 20 and 24, the number between ages 25 and 29, and so on.

Building a query that calculates ages from Now using DateDiff for a single age range and producing a count of the records within that range is not a problem. What I can't get access to do is produce results for multiple age ranges in the same query.

I appreciate any guidance you can provide. Thanks.

 

[spikepl]

age \ 5 would give you an index to group on and then you could count each group

 

[boblarson]

I made a sample for you so you could see how to do it (the formula isn't necessarily intuitive because a person's age can be different in the same year depending on when you check and the date on which their birthday falls.

 

[Baldlegal]

Thank you so much. Your solution works and now I just have to figure out how you did it!

 

[boblarson]

I just have to figure out how you did it!

You should be able to use the entire IIF part of each field in the query for your purposes. I purposely used the field name of BDate that you had so you could. All you would need to do is to change the label of the fields and the numbers which it is using -

like the

Between 41 And 50

if you wanted 39 to 48 you would change it to

Between 39 And 48

(it is INCLUSIVE so whatever you specifiy in the Between, will be what it goes from and to).

And basically this used a query which uses the Grouping/SUM and it uses an IIF statement to determine if it falls within that range and if it does it returns a 1 and if not it returns a 0. Then it sums up the values and that provides the count.

 

[Beetle]

Just for grins, here is another option.

Given a simple query (let's say qryAges) that returns , for example, the BDate and Age, you could create another query against that using the Partition function. SQL might look like;

SELECT Partition([Age],0,100,5) AS Range, Count(qryAges.Age) AS TotalCount
FROM qryAges
GROUP BY Partition([Age],0,100,5);

Results would look like the attached image.

 

[boblarson]

Just for grins, here is another option.

Given a simple query (let's say qryAges) that returns , for example, the BDate and Age, you could create another query against that using the Partition function. SQL might look like;

SELECT Partition([Age],0,100,5) AS Range, Count(qryAges.Age) AS TotalCount
FROM qryAges
GROUP BY Partition([Age],0,100,5);

Results would look like the attached image.

Yeah, Partition is highly overlooked. It works great if you don't need to have specific wording for the ranges and you have equal buckets to drop things into.

One thing I would point out since you used AGE as the field - I would hope that was a calculation using this formula (or similar):

DateDiff("yyyy",[Date of Birth],Now())+Int(Format(Now(),"mmdd")<Format([Date of Birth],"mmdd"))

So that the age is calculated correctly.

 

[Beetle]

One thing I would point out since you used AGE as the field - I would hope that was a calculation using this formula (or similar):

Yes, I just have a small function that calculates the age properly. I didn't mention that because the OP said he already had a query that calculated age. Hopefully he is using something similar to get the correct age, as you say.

 

[boblarson]

Hopefully he is using something similar to get the correct age, as you say.

I hope so too.