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

ID: Q180662

The information in this article applies to:

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:

Q94397 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.

Open the sample database Northwind.mdb.

Create a module and type the following lines in the Declarations section:
```
      Option Explicit
      Global Cntr As Integer 
```

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

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

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.

Keywords : QryGnrl QryHowto
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto