Microsoft KB Archive/180662

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

Article ID: 180662

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q180662



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



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:

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

Keywords: kbhowto KB180662

-

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

© Microsoft Corporation. All rights reserved.