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

>
Want a good read? Try The Summer of His Life (available on Amazon!)

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: 261
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
>
Want a good read? Try The Summer of His Life (available on Amazon!)

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


Joined: 20 Feb 2008
Status: Offline
Points: 261
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

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.