ACC: Using a Query to Return Every Nth Record from a Table (95/97) (180662)

MORE INFORMATION

Method 1

You can create a query based on a table that contains an AutoNumber field with the criteria Like "*0" on the AutoNumber field to return every 10th record. Similarly, you can use the criteria Like "*00" to return every 100th record. You can also use the Mod operator to return every record whose AutoNumber field is evenly divisible by any other number. The problem with this method is that it only returns matching values. It is not dependent on the physical position of the records within the table. If there are gaps between the values in the AutoNumber field because records have been deleted, you won't get an accurate representation of every Nth record.

Method 2

This method uses a dynamic counter and does not require an AutoNumber field in the table.

CAUTION: Please familiarize yourself with the issues when using a dynamic counter in a query as described in the following article in the Microsoft Knowledge Base before continuing with this method:

94397 ACC: Adding Dynamic Counter to Query to Count Records

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

1. Open the sample database Northwind.mdb.
2. Create a module and type the following lines in the Declarations section:
   

         Option Explicit
         Global Cntr As Integer
   						

3. Type the following procedures:
   

         '*************************************************************
         ' Function:  NthRec(Z As String, Nth As Integer) As String
         '
         ' Purpose: This function will return an "X" to the query for
         ' every Nth record as specified by the parameter in the query.
         '*************************************************************
   						

         Function NthRec(Z As String, Nth As Integer) As String
            Cntr = Cntr + 1
            If Cntr Mod Nth = 0 Then
               NthRec = "X"
            End If
         End Function
   
         '**************************************************************
         ' Function:  SetToZero()
         '
         ' Purpose: This function will reset the global Cntr to 0. This
         ' function should be called each time before running a query
         ' containing the Qcntr() function.
         '**************************************************************
   
         Function SetToZero()
            Cntr = 0
         End Function
   
         '**************************************************************
         ' Function:  RunNthQuery()
         '
         ' Purpose: This function runs the SetToZero function and then
         ' opens the qryEveryNthRecord query in Datasheet view.
         '**************************************************************
   
         Function RunNthQuery()
            SetToZero
            DoCmd.OpenQuery "qryEveryNthRecord"
         End Function
   						

4. Create the following new query based on the Orders table:
   

         Query: qryEveryNthRecord
         -----------------------------
         Type: Select Query
   
         Field: OrderID
           Table: Orders
         Field: CustomerID
           Table: Orders
         Field: OrderDate
           Table: Orders
         Field: Freight
           Table: Orders
         Field: NthRec([OrderID],[What Nth Would You Like Today?])
           Show: No
           Criteria: X
   						

5. Press CTRL+G to open the Debug window, type the following line, and then press ENTER:
   

   ?RunNthQuery()

   
   Note that the qryEveryNthRecord is opened and displays every Nth record from the Orders table that you typed in the "What Nth Would You Like Today?" parameter.