Age bracket query

morlan · Jul 11, 2003

I'm doing a query which filters records of people that are aged 18 - 25. This works fine but I want to to a query which will retun a range of ages as opposed to just one age bracket.

When I run this query it will only return the ages of people ages 18 - 25. I want it to also return ages 26 -35, 36 - 45 etc

I'm having trouble getting this work. Here is the current sql query

Code:

SELECT tblMain.ID, Count(tblMain.CustDOB) AS CountOfCustDOB
FROM tblMain
GROUP BY tblMain.Venue, DateDiff("yyyy",[CustDOB],Now())
HAVING (((DateDiff("yyyy",[CustDOB],Now()))>=18 And (DateDiff("yyyy",[CustDOB],Now()))<=25));

dcx693 · Jul 11, 2003

There are a few ways to do this, including some really clever query things, that some other people might be able to suggest. One way is create a calculated field in your query that calculated everyone's age. You can have that same column or a different column give you the range in the form "18-25", "26-35", etc...

My preferred method is illustrated in the attached database. No complicated query formulas. Check it out, and if you need more info, post back.

dcx693 · Jul 11, 2003

You can also try using the Partition function like this:
SELECT DISTINCTROW Partition(DateDiff("yyyy",[CustDOB],Date()),1,120,9) AS Range, Count(tblMain.CustDOB) AS [Count]
FROM tblMain
GROUP BY Partition(DateDiff("yyyy",[CustDOB],Date()),1,120,9);

It's convenient, however, that won't give you the exact age ranges you want.

Jon K · Jul 12, 2003

There is an accurate expression for Age:-

DateDiff("yyyy", [CustDOB], Date()) + Int(Format(Date(), "mmdd") < Format([CustDOB], "mmdd"))

dcx693 · Jul 12, 2003

Very cool. I was too lazy to come up with that.

morlan · Jul 14, 2003

dcx693 said:
There are a few ways to do this, including some really clever query things, that some other people might be able to suggest. One way is create a calculated field in your query that calculated everyone's age. You can have that same column or a different column give you the range in the form "18-25", "26-35", etc...

My preferred method is illustrated in the attached database. No complicated query formulas. Check it out, and if you need more info, post back.

Absolutley perfect!

Thanks

morlan · Sep 30, 2003

dcx693:

I am trying to use this query on SQL but it doesn't understand the DATE function.

The exact error is:

ADO Error: Date is not a recognised function name

Do you know what the translation would be?

morlan · Sep 30, 2003

morlan said:
dcx693:

I am trying to use this query on SQL but it doesn't understand the DATE function.

The exact error is:

ADO Error: Date is not a recognised function name

Do you know what the translation would be?

Sorry, stupid me! The funtion is GETDATE

Age bracket query

morlan

Registered User.

dcx693

Registered User.

Attachments

dcx693

Registered User.

Jon K

Registered User.

dcx693

Registered User.

morlan

Registered User.

morlan

Registered User.

morlan

Registered User.

Similar threads

Users who are viewing this thread