ACC2000: Using Query to Return Every Nth Record from a Table

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. But before you try the sample code, there are some limitations to this method that you should consider:

• You must reset the global counter to 0 before you run the query each time. You can use the function SetToZero() to do this.
• Any scrolling, minimizing, maximizing, switching focus away and back, and so on, causes a repaint to occur, which in turn causes the query to call the function again. For this reason, you should not use this function in a select query. Instead, you should base a report on the select query or run a make-table query and view the resulting table.
• When basing a report on a select query with this function, scrolling back through the pages will cause the function to be called multiple times, and the counter expression will be off by the total number of records in the recordset.
• When you place criteria on the counter expression, the function will be called twice per record. For example, if you specify criteria of <=10 to return the first 10 records, the recordset will contain 10 records, but the counter expression will be off by the total number of records in the recordset.
• Do not place the counter expression in the first column of the query; the number will be off by one.
• Do not combine criteria and sorting in a query with the counter expression. The criteria will be applied before the sort. Instead, you must create a make-table query with the sort, and then base a second query containing the counter expression on the new table with sorted data.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow 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 Immediate 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.