Microsoft KB Archive/132138

= ACC2: Grouping Records into Sets of Records Using a Query =

Article ID: 132138

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q132138



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:

<pre class="fixed_text">     Macro Name             Actions ---     TestGroupLevelMacro    RunCode OpenQuery

TestGroupLevelMacro Actions --     RunCode Function Name: SetGroupLevelReset OpenQuery Query Name: TestGroupLevel Data Mode: Add </li>  To test the results, run the TestGroupLevelMacro macro. Note that the following results are displayed after the TestGroupLevel query runs:

<pre class="fixed_text">     SumOfQuantity  MyGroup --     2187           0      2248           1      2175           2      2262           3      1799           4      2307           5      2363           6                        </li></ol>

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.

<div class="references_section">