Microsoft KB Archive/207651

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

Article ID: 207651

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207651



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.



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.

 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.</ol>

Keywords: kbhowto KB207651

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.