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 .<
|