Splitting into age groups

[fredElliotRules]

Hi,

I have a dataset with an age field which just stored an age. I want to run a query which will calculate the frequency based on all age groups 20-30,30-40.

Does anyone have any ideas

 

[chergh]

Hi,

I have a dataset with an age field which just stored an age. I want to run a query which will calculate the frequency based on all age groups 20-30,30-40.

Does anyone have any ideas

Easiest way of the top of my head would be:

SELECT count(age)
FROM table
HAVING ((count(age))>=20 AND (count(age))<=30)

Set up one of these queries for each age range. You could tie them all together using a macro and make them append queries etc. There may be a more eloquent solution but this is just of the top of my head and I have a meeting to goto.

 

[fredElliotRules]

Thanks matey but

mysql = "SELECT (Int([age]/10)*10) AS MYG, Count([MYG]) AS Frequency " & _
"FROM Data " & _
"GROUP BY (Int([age]/10)*10);"

works great, just got to fgure out how to append values in ADODB.recordset now.

 

[raskew]

Hi -

Age is a 'moving target' and as such shouldn't be stored in a table but rather expressed as a calculated field in a query. For example, if your age is stored in a table, based on DOB, and your birthday is tomorrow then come tomorrow the table is going to be inaccurate

Consider the Partition() function to categorize your age groups. Because of the Age calculation, it'll take two queries. I'm using tblClients, which includes fields:
ActiveRecord - yes/no
DOB (date of birth) - Date/time

The first query (query7) returns the current age (as of today) of all ActiveRecords (clients)

SELECT
    tblClients.ActiveRecord
  , DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>=Format(Date(),"mmdd")) AS Age
FROM
   tblClients
WHERE
   (((tblClients.ActiveRecord)=True));

The second query (query8), a cross-tab query, incorporates the Partition() function to categorize the ages in Query7, which in my example may range from 5 to 30, in 5 year brackets, i.e. 5 - 9, 10 - 14, 15 - 19, etc.for ActiveRecords (clients). Lookup this function in the Help file to see how it's structured. It can be tricky and takes a little concentration but following the Help file examples should lead you where you need to go.

TRANSFORM Count(query7.ActiveRecord) AS CountOfActive
SELECT
    query7.ActiveRecord
FROM
   query7
GROUP BY
   query7.ActiveRecord 
PIVOT Partition(Int([Age]),5,30,5);

HTH - Bob