Sorting the age goups

In my query are date of birth and age of pupils. In the report i have show how many pupils for the particular age group like (under 5s, 5-7 or 8-11)coming for visit. I don't know how to connect age of pupils or DOB to any of that age group. Can you help me please?

You could put a series of IIFs in your query, e.g.

AgeGroup: iif([PupilAge] < 5, "Under 5", iif([PupilAge] <= 7, "5-7", "8-11"))

Alternatively, you could write a function which does the same kind of thing and use that in the query. If you need to use the Age Group in multiple places in your system, I would recommend the function route as it would give consistenmcy in reporting.

Yup.
So in your query will look something like this

SELECT <field1>, <field2>, ..., iif([PupilAge] < 5, "Under 5", iif([PupilAge] <= 7, "5-7", "8-11")) AS ageGroup, ....
FROM .....

If your report needs to show each student and then a total of how many are in the group modify your query using the function from Sherrington like so:

SELECT <field1>, <field2>, ..., iif([PupilAge] < 5, "Under 5", iif([PupilAge] <= 7, "5-7", "8-11")) AS ageGroup, ....
FROM .....


and then in the reprort use the ageGroup to Group and put a total in the Group footer.

If you just need a report that has how many students are in each group then the query would look like this.

SELECT Count(PupilAge) AS NumInGroup, IIf([PupilAge]<5,"Under 5",IIf([PupilAge]<=7,"5-7","8-11")) AS ageGroup
FROM <tablename>
GROUP BY iif([PupilAge] < 5, "Under 5", iif([PupilAge] <= 7, "5-7", "8-11")) ;

Thanks, sherrington and dykebert it working well!!!
It looks very easy, but when you don't know it looks very difficult.
thanks again.