SQL is a very powerful query language that is built into Microsoft Access.
But one thing SQL does not do very well is refer to a previous row to calculate
values. As a result, some things that are simple to do in a spreadsheet are
difficult in a query. On of those is creating a numbered sequence for your
query. However, difficult does not mean impossible. This article will show you
how to create a numbered query.

A numbered query is a query where each record is numbered sequentially. There
may be a variety of reasons to do this. You might have a test question database
where you want each question to be automatically numbered. Or you might want to
export a customer list with sequential numbering to an external source like
Excel. While this is simple to do in an Access report, it requires some advanced
techniques to do in a query.

But suppose you don't want to do it in a report. Suppose you want to do it
directly in a query. There are two different ways to accomplish this. The first
uses the Domain Aggregate Function DCount and the second uses a
Correlated Subquery.

Both of these methods require a unique column in the table to create the
sequence on. This could be the Primary Key field or any field that has a Unique
Index. In the following example, the Customers table has two such columns,
CustID (Customer ID), which is the primary key, and CustName (Customer Name),
which has a unique index... (read more)...(and even more)

This sample illustrates two different ways to create a query with a sequenced number column.

You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot delete your posts in this forumYou cannot edit your posts in this forumYou cannot create polls in this forumYou cannot vote in polls in this forum