Query to make a report

[aca00js]

I have a table called Offender. It has columns OffenderID (Autonumber), Surname, Forename, DOB.

What I want to do is to work out the age of each offender and group them into relative age groups:

Less Than 21, 21 to 35, 36 to 50, 51 to 74 and 75 plus.

These totals shall be then be converted to percentage of the total offenders and using Excel be placed into a graphical format.

I have used the query expression of DateDiff(Offender.DOB,Date()) t ocalculate there age. But don't know how to us it to make my report.

 

[dcx693]

How do you want to show the data in your report?

If you want to show age groupings, I have 3 suggestions: A simple way to do this is to group them in the query that is the recordsource for the report.

You can have a column in your query that says one of the following:
<21
21-35
36-50
51-74
75+
I would write a custom VBA function for that rather than attempt it with an IIF function. Then do some grouping in your report to show them together (from the View menu, choose Sorting and Grouping).

My second suggestion: You could also write a query that groups them by having a table with just age ranges like this:
low high range
 0  21  under 21
21  35  21-35
35  50  36-50
etc....
then create a select query that uses syntax like this:
SELECT tblPeople.OffenderID, tblPeople.Surname, tblPeople.Age, tblAgeRanges.Range FROM tblAgeRanges, tblPeople WHERE (((tblPeople.Age)>=[tblAgeRanges].[Low] And (tblPeople.Age)<=[tblAgeRanges].[High]));
Again, do some grouping in your report to show them together.

Another interesting method is to base a report off a crosstab query (kind of like a pivot table in Excel). Not enough space to go over that here!

 

[aca00js]

I'm new to Access so I would appricate it if you could tell me how to do this.

Thanks