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

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you two methods that you can use to return every Nth record from a table. The first method is the simplest and uses an AutoNumber field. But this method is not always reliable. The second method is a little more complex, but it is reliable. It uses a dynamic counter in a query.

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:

   ARTICLE-ID: Q94397
   TITLE     : ACC: Adding Dynamic Counter to Query to Count Records

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.