ACC2: Grouping Records into Sets of Records Using a Query

ID: Q132138


The information in this article applies to:


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 


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 

Last Reviewed: April 26, 1999