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.
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=309
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, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .
 

NumberedQuery

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... ( http://rogersaccessblog.blogspot.com/2016/06/how-do-i-create-numbered-query-in-access.html" rel="nofollow - read more )...( http://rogersaccessblog.blogspot.com/2010/12/domain-function-examples-numbered-query.html" rel="nofollow - and even more )


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

Download
Access 2000 (.mdb): http://www.rogersaccesslibrary.com/forum/uploads/5/NumberedQuery2k.zip" rel="nofollow - uploads/5/NumberedQuery2k.zip    Sample with Documentation.
Access 2010 (.accdb):  uploads/5/NumberedQuery2010.zip" rel="nofollow -   uploads/5/NumberedQuery2010.zip" rel="nofollow - Sample with Documentation.



-------------
Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<



Replies:
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, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<



Print Page | Close Window