ACC2: Grouping Records into Sets of Records Using a Query (132138)

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
   						

The results display the sum of the values in the Quantity field in each set of 100 records. To change the grouping value, change the value of the Const MyIncNum statement in the SetGroupLevel() function.

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.