Print Page | Close Window

NumberedQuery.mdb (beginner)

Printed From: Roger's Access Library
Category: Roger's Access Library
Forum Name: Roger's Download Samples
Forum Description: A compendium of Microsoft Access 97 and 2000+ routines, each separated into its own sample database.
Printed Date: 21 Oct 2017 at 11:52am

Topic: NumberedQuery.mdb (beginner)
Posted By: Roger Carlson
Subject: NumberedQuery.mdb (beginner)
Date Posted: 30 Jul 2008 at 10:32pm

Please consider donating $1 per sample downloaded," rel="nofollow - find out why here .


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... (" rel="nofollow - read more )...(" rel="nofollow - and even more )

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

Access 2000 (.mdb):" rel="nofollow - uploads/5/    Sample with Documentation.
Access 2010 (.accdb):  uploads/5/" rel="nofollow -   uploads/5/" rel="nofollow - Sample with Documentation.

Consider donating $1 per sample downloaded," rel="nofollow - find out why here .<

Posted By: Roger Carlson
Date Posted: 16 Jun 2016 at 8:02am
Updated.  Added Access 2010 (.accdb) version and NEW documentation.

Consider donating $1 per sample downloaded," rel="nofollow - find out why here .<

Print Page | Close Window