databasedev.co.uk - database solutions and downloads for microsoft access

Grouping Data in a Microsoft Access Report:

Creating a Report to Group Data by Age Ranges - this article explains how we can create a report that allows us to group the data on non-standard numeric ranges. If we use the Microsoft Access Report Wizard, when working through the stages and selecting fields to Group the report on, we can choose Group Intervals. If the field that we are grouping on is numeric, we are presented with the following choices to group on:

Microsoft Access Report wizard screen, showing the Grouping Intervals available for numeric data

In our report, we want to work with grouping intervals that are different to those choices available with the report wizard. We would like to group our data by age ranges, for example 0-17, 18-25, 26-30, 31-35 etc.

The following shows how we create the report that uses our chosen group intervals:

Our report is going to be based upon Employees. In our Employee table we include various data, and specifically we include an Employee Date Of Birth. This information is going to be used to calculate our Age value, but we also need to create a field that includes the data that we are going to group on.

For this, we create a Function, that will then be used in the query design. The function uses a DateDiff calculation to calculate the Age, and also includes a SELECT CASE statement that will create the Age Group Ranges. Below, you will see the Function that we used in this example:

'----------------------------Code Start--------------------------------
Public Function AgeGroup(dtmBirthDate As Date) As String


    Dim intAge As Integer

    'Age Calculation
    intAge = DateDiff("yyyy", [dtmBirthDate], Now()) + _
             Int(Format(Now(), "mmdd") < Format([dtmBirthDate], "mmdd"))

    Select Case intAge

        'For each Age range, write out Age Group (used in qry)
    Case 0 To 17
        AgeGroup = "0-17"
    Case 18 To 25
        AgeGroup = "18-25"
    Case 26 To 30
        AgeGroup = "26-30"
    Case 31 To 35
        AgeGroup = "31-35"
    Case 36 To 40
        AgeGroup = "36-40"
    Case 41 To 45
        AgeGroup = "41-45"
    Case Is > 45
        AgeGroup = "46+"
    End Select

End Function
'----------------------------Code End-----------------------------------

In our query design, we add a new field to the query, that uses this function to add the AgeGroup to each record in the query, dependant on the value in the Age field. Our query design is shown below:

Here we use the AgeGroup Function in our query, to return the appropriate Age Group Range value

This returns the following results when we display our query datasheet:

Here we see our new field, displaying the Age Group Range

Now, when we build our report, either manually or by using the Microsoft Access Report Wizard, we can use the AgeGrps field to create our Grouping on:

Using the AgeGrps field to Group the report on.

When we run the report, this demonstrates how the grouping will appear:

A sample of the Microsoft Access report, including the grouping

An example of this database can be found in the Microsoft Access downloads section or the Microsoft Access Reports section. This example is created in Microsoft Access 2000.