Grouping of data

[tkandy]

I have a table with the following



Name, SName, DOB, Area, Gender


From the DOB field i get the age, and i want to be able to show a grouping for each area with the following count in catagories. And males and females

17-25yrs, 26-35yrs, 36-45yrs, 46+yrs and Total



So something like this

17-25 yrs 26-35yrs 36-45yrs 46+Yrs Total
Females 5 2 7
Males 6 3 9
Total 11 3 2 0 16

[kennejd]

What have you got so far?

[ajetrumpet]

What have you got so far?

I LIKE this!!! Kudos to this post!!!

[LionKing]

tkandy,

FYI
1. try to search first in google/microsoft on how to get the age
2. use if's.... iif getAge >=17 and age <=25,"17-25yrs", iif getAge >=26 and age <=35,"26-35yrs",....... as AgeBracket; sum(iif(gender = "F",1,0)) as TotalFemale; sum(iif(gender = "M",1,0)) as TotalMale GROUP BY agebracket...
3. use different query or function to get the overall total

[kennejd]

I can't claim originality....I seem to have seen similar posts from some 'aje' guy...

[tkandy]

Thank you to all that posted, i started doing seperate queries for each one as i am not fluent i knew their would be a better way

the row that is like this i did have it in a table (17-25 yrs 26-35yrs36-45yrs46+YrsTotalFemales527Males639Total 1132016)
i have now changed this to look like this


17-25 yrs 26-35yrs 36-45yrs 46+Yrs Total
Females 5 2 7
Males 6 3 9
Total 11 3 2 0 16

[tkandy]

Hi Lion king

I already have the age sorry i missed this bit field out sorry.

[LionKing]

Hello tkandy,

Okay.. good if you have already function to get the age...

In your new sequence of age columns... same thing you have to do... in your query to group the ages..
(use SELECT ang GROUP BY query)

(column1) 17-25yrs: iif(getAge >=17 and age <=25,1,0)
(column2) 26-35yrs: iif getAge >=26 and age <=35,1,0)
(column3) ......
replace group by with sum

you can include totals if do you want...

(column 5) Females: iif(Gender = "f",1,0)
(column 6) Males: iif(Gender = "m",1,0)
replace group by with sum

For total
Option 1 (with age limit):
(column 7) Total: iif(getAge >= 17,1,0)
replace group by with sum

Option 2 (no age limit):
(column 8) Total: Gender
replace group by with count

if you are going to use total in form or report separately:
Females: dcount("[gender]","table_name","[gender] = 'F'")
Males: dcount("[gender]","table_name","[gender] = 'M'")
Total: dcount("*","table_name", "getAge([dob])>= 17") // with age limit
Total: dcount("*","table_name") // no age limit

good luck!