Sort Calculated Ages in to Age Groups

[l3111]

Hi I've searched high and low for this answer, i've tried formula after formula but to no avail.



I have created a query that calculates customers ages from thier DOB
Age: DateDiff("yyyy",[DOB],Now())

Now my problem arises as I need to report this in groups, more precisily these groups:
18-24
25-34
35-44
45-54
55-64
65-74
75-84
85-94
95+

There doesn't seem to be a thread on this site that explains how best to do this. I'm using my novice head to think query, but these IIF formulas are confuuuuuusing!!

As always, all help greatly appreciated, this forum has been a life saver with my current work project

l3111, Manchester, UK

[alansidman]

You would be able to achieve this simply in a form creating the group with some VBA. Here is a link to how to do a Case Select Statement.

http://www.techonthenet.com/access/f...anced/case.php

Once you have established your grouping, then you can do an aggregate query on the groups

Alternatively, you could create a cross tab query based upon the groups and then aggregate the query.

Alan

[l3111]

FIXED IT

Age Group: IIf([Age]>84,"85+",IIf([Age]>74,"75-84",IIf([Age]>64,"65-74",IIf([Age]>54,"55-64",IIf([Age]>44,"45-54",IIf([Age]>34,"35-44",IIf([Age]>34,"25-34",IIf([Age]>24,"18-24")))))))) as a field


then use this query to create a new query

with these fields
Age Group (field calculated from previous query) - Group By
Age Group - Count