Roger's Access Library Homepage
Forum Home Forum Home > Roger's Access Library > Roger's Download Samples
  New Posts New Posts RSS Feed - NumberedQuery.mdb (beginner)
  FAQ FAQ  Forum Search   Events   Register Register  Login Login


Please consider donating $1 per sample downloaded, (find out why here)

NumberedQuery.mdb (beginner)

 Post Reply Post Reply
Author
Message Reverse Sort Order
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 249
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Topic: NumberedQuery.mdb (beginner)
    Posted: 16 Jun 2016 at 8:02am
Updated.  Added Access 2010 (.accdb) version and NEW documentation.
Consider donating $1 per sample downloaded, find out why here.<
Back to Top
Sponsored Links


Back to Top
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 249
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Posted: 30 Jul 2008 at 10:32pm

Please consider donating $1 per sample downloaded, 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... (read more)...(and even more)


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

Download
Access 2000 (.mdb): uploads/5/NumberedQuery2k.zip   Sample with Documentation.
Access 2010 (.accdb):  uploads/5/NumberedQuery2010.zip Sample with Documentation.

Consider donating $1 per sample downloaded, find out why here.<
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down