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

ID: Q207651

The information in this article applies to:

Microsoft Access 2000

This article applies only to a Microsoft Access database (.mdb).

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

SUMMARY

This article shows you two methods that you can use to return every Nth record from a table. The first method is the simplest. It uses an AutoNumber field, but this method is not always reliable. The second method is reliable although a little more complex. 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. 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: 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 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.

Additional query words:


Keywords          : kbdta QryGnrl QryHowto 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: May 13, 1999