ACC2: Grouping Records into Sets of Records Using a Query

ID: Q132138

The information in this article applies to:

Microsoft Access 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.

Create a new module and type the following lines in the Declarations section:
```
      Option Explicit
      Dim MyVal As Long, MyValSort As Long 
```

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

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

Save and close the TestGroupLevel query.

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

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.

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:

Q94397 ACC: Adding a Dynamic Counter to a Query to Count Records (1.x, 2.0)

Keywords : kbusage QryTotal
Version : 2.0
Platform : WINDOWS
Issue type : kbhowto