Tables and queries are the foundation of any Access database. Tables store the data and queries retrieve the data we need to view and manipulate. Usually, criteria expressions can handle even the most complex requirements, such as returning every nth record. Many developers tend to use a simple VBA function, but a criteria expression can handle this particular task.A criteria expression solution
First, let's look at a criteria expression that returns every nth record in the Orders table in Northwind (the example database that comes with Access). First, you need to base a Select query on the Orders table, as follows:
Access 2007 and 2010
Now, you're ready to build the query. First, drag a few fields to the grid: OrderID, CustomerID, and EmployeeID. You can add as many fields as you need, but you need at least one field that uniquely identifies each record—a primary key field most likely. When applying this to your own queries, make sure you can include such a field.
Now you're ready to add the actual criteria expression that identifies every nth record. This expression takes the following form:
DCount("fieldid","table","fieldid <= " & [fieldid]) Mod n
The Mod operator returns a value for each record. Update n to determine nth. For example, to return every fifth record from the Orders table, use the following expression:
DCount("OrderID","Orders","OrderID <= " & [OrderID]) Mod 5
If you're using Access 2007 and 2010, be sure to include a space before the ID string in each field name and to enclose the field name in brackets as follows:
DCount("[Order ID]","[Orders]","[Order ID] <= " & [Order ID]) Mod 5
Next, choose Totals from the View menu; in Access 2007 and 2010, click Totals in the Show/Hide group on the Design tab. Set the Total aggregate to Expression for every cell except the criteria column. For that column, set the Total aggregate to Expression. (Simply choose the aggregate from the cell's dropdown list.) Then, add the criteria 0 to the criteria column's Criteria cell. Click Run to execute the query. A quick look at the OrderID field verifies that the query is indeed retrieving only every fifth record. Remember, this is the table's primary key field and therefore, in sequential order.
As I mentioned earlier, this query has a few limitations:
- You need a unique value for each record and those values must be in sequential order.
- Many developers shy away from DCount() because they claim it's slow, but others debate that charge and suggest an appropriate index is all that's needed to improve performance.
If you choose not to use the DCount() solution, you can use VBA. Specifically, you call a function from the query and use the Mod operator on the function's results, in the same way.
Open the Visual Basic Editor (VBE) by pressing [Alt]+[F11]. Choose Module from the Insert menu and enter the following function:
Function SeqValues(var As Variant)
'Return a sequential column of values for
'nth record query.
Static i As Integer
i = i + 1
SeqValues = i
Next, drag OrderID, CustomerID, and EmployeeID to the grid, as you did before. Instead of entering the criteria expression in the last solution, enter the following call to SeqValues():
SeqValues(OrderID) Mod 5
In Access 2007 and 2010, use the expression
SeqValues([Order ID]) Mod 5
Enter 0 in the Criteria cell, as shown below. In addition, this query is not a Totals view. Click Run to execute the query; the results are the same. The call to SeqValues creates a list of sequential values, which is the same thing the DCount() function in the earlier solution does.
If this is a frequent task for users, consider creating a form that lets them pass the nth value and then executes the query.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.