Creating Age Bands in a query

[CutAndPaste]

I have a query that calculates Clients Ages in whole years. I want to be able to group my query output into a report grouped by age bands, say <16,16-20,21-30, >31 etc. and then be able to use the results of this in other groupings e.g. Gender by Age Band, or Favourite Fruit etc.

How can I create these age bands in a query? what's the SQL?

 

[Pat Hartman]

Select IIf(ClientAge < 16, "Grp1 <16", IIf(ClientAge < 21, "Grp2 16-20", IIf(ClientAge < 30, "Grp3 21-30", "Grp4 >30"))) As AgeBand
From YourTable;

I included "Grpx " in front of each age band literal so that you could sort the data by age band and have them come out in ascending order.

 

[CutAndPaste]

Pat,

Worked a treat, many thanks!