ACC2: Grouping Records into Sets of Records Using a Query

• Microsoft Access version 2.0

SUMMARY

This article demonstrates a method that you can use to create a report that groups records into sets of records according to a user-supplied number. To demonstrate this method, this article uses the Orders Details table in the sample database NWIND.MDB to create groupings of 100 records. In each set of 100 records, the Quantity fields are summed into one value. In your own reports, you can change the grouping value to any number you want.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

To create a report that creates groupings of a specific number of records, follow these steps.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

1. Create a new module and type the following lines in the Declarations section:

         Option Explicit
         Dim MyVal As Long, MyValSort As Long
   

2. Type the following two functions:

         ' ******************************************************
         ' Function: SetGroupLevel
         '
         ' Purpose: This function creates groups of records
         '          containing n records. The n is defined
         '          by the Const MyIncNum statement.
         ' ******************************************************
   
         Function SetGroupLevel (pId As Variant) As Integer
   
         ' Set the value for the number of records to group.
         Const MyIncNum = 100
   
         ' Compare the value of MyVal to the custom value
         ' and if true, increment the group value by one.
         If MyVal = MyIncNum Then
            MyValSort = MyValSort + 1
            MyVal = False
         End If
   
         ' Continue to increment counter.
         MyVal = MyVal + 1
   
         ' Pass back the custom group value to query.
         SetGroupLevel = MyValSort
   
         End Function
   
         ' ******************************************************
         ' Function: SetGroupLevelReset
         '
         ' Purpose: This function is called to reset the counter
         '          values used in the SetGroupLevel() function.
         ' ******************************************************
         Function SetGroupLevelReset () As Integer
            MyVal = False
            MyValSort = False
         End Function
   
   

3. Create a new query as follows, and add the Order Details table:

         Query: TestGroupLevel
         ---------------------
         Type: Totals Query
            Field: Quantity
            Table: Order Details
            Total: Sum
         Field: MyGroup: SetGroupLevel([Quantity])
            Total: Group By
   
   

4. Save and close the TestGroupLevel query.

5. Create the following macro to show the results:

         Macro Name             Actions
         -------------------------------
         TestGroupLevelMacro    RunCode
                                OpenQuery
   
         TestGroupLevelMacro Actions
         --------------------------------------
         RunCode
            Function Name: SetGroupLevelReset()
         OpenQuery
            Query Name: TestGroupLevel
            Data Mode: Add
   
   

6. To test the results, run the TestGroupLevelMacro macro. Note that the following results are displayed after the TestGroupLevel query runs:

         SumOfQuantity  MyGroup
         ----------------------
         2187           0
         2248           1
         2175           2
         2262           3
         1799           4
         2307           5
         2363           6
   
   

NOTE: Keep in mind that the last row will be the remainder of available records minus the number you specify for the MyIncNum variable. Note that this example shows 2150 records producing 20 rows of 100 records and that the final row consists of the remaining 50 records.

REFERENCES

Microsoft Access "Building Applications," version 2.0, Chapter 5, "Access Basic Fundamentals"

For more information about dynamic query numbering, please see the following article in the Microsoft Knowledge Base: