How to customize the numbering in a query?

I want to customize the numbering in a query. In other word, I want to make
customized numbering in a column in query but as formatted below:
001
002
003
…
…
etc
any help please?

Here's an example using a Transactions table which numbers the transactions
sorted by date:

SELECT FORMAT(COUNT(*),"000") AS RowCounter,
T1.TransactionDate, T1.TransactionAmount
FROM Transactions AS T1 INNER JOIN Transactions AS T2
ON ( T2.TransactionID = T1.TransactionID
OR T2.TransactionDate T1.TransactionDate)
AND (T2.TransactionDate = T1.TransactionDate)
GROUP BY T1.TransactionDate, T1.TransactionAmount, T1.TransactionID;

Note how the unique TransactionID is brought into play to differentiate
between two or more transactions on the same date.

Ken Sheridan
Stafford, England

Jon wrote:
I want to customize the numbering in a query. In other word, I want to make
customized numbering in a column in query but as formatted below:
001
002
003
…
…
etc
any help please?

--
Message posted via http://www.accessmonster.com

Hello Jon,

The table in your query must contain a unique index such as an autonumer
field. As an example in a report of customers, CustomerID would be a unique
field used in the query.
Example: To list all customers in the table, "TblCustomer" and auto number
the output rows, you would enter the following in a blank field of the
customers query:
RowNum: (Select Count (*) FROM [TblCustomer] as Temp
WHERE [Temp].[CustomerID] [TblCustomer].[CustomerID])+1

To get the format you want, you would then need to add another field to your
query:
CustomRowNum:Format([RowNum],"000")

Steve



"Jon" wrote in message
...
I want to customize the numbering in a query. In other word, I want to make
customized numbering in a column in query but as formatted below:
001
002
003
.
.
etc
any help please?

On Sat, 22 May 2010 19:18:58 -0400,
"Steve" wrote in message



Hello Jon,

The table in your query must contain a unique index such as an autonumer
field. As an example in a report of customers, CustomerID would be a unique
field used in the query.
Example: To list all customers in the table, "TblCustomer" and auto number
the output rows, you would enter the following in a blank field of the
customers query:
RowNum: (Select Count (*) FROM [TblCustomer] as Temp
WHERE [Temp].[CustomerID] [TblCustomer].[CustomerID])+1

To get the format you want, you would then need to add another field to your
query:
CustomRowNum:Format([RowNum],"000")

Steve



"Jon" wrote in message
...
I want to customize the numbering in a query. In other word, I want to make
customized numbering in a column in query but as formatted below:
001
002
003
.
.
etc
any help please?



test

No need for a separate column; just format the return value of the subquery,
e.g.

SELECT FORMAT(
(SELECT COUNT(*)
FROM Transactions AS T2
WHERE T2.TransactionDate = T1.TransactionDate
AND ( T2.TransactionID = T1.TransactionID
OR T2.TransactionDate T1.TransactionDate)),"000") AS RowCounter,
T1.TransactionDate, T1.TransactionAmount
FROM Transactions AS T1
ORDER BY T1.TransactionDate, T1.TransactionID;

or:

SELECT
(SELECT FORMAT(COUNT(*),"000")
FROM Transactions AS T2
WHERE T2.TransactionDate = T1.TransactionDate
AND ( T2.TransactionID = T1.TransactionID
OR T2.TransactionDate T1.TransactionDate)) AS RowCounter,
T1.TransactionDate, T1.TransactionAmount
FROM Transactions AS T1
ORDER BY T1.TransactionDate, T1.TransactionID;

A JOIN should perform better than a subquery, however.

For an updatable query:

SELECT FORMAT(
DCOUNT("*", "Transactions",
"TransactionDate = #"
& Format(TransactionDate,"yyyy-mm-dd")
& "# AND (TransactionID = " & TransactionID
& " OR TransactionDate #"
& Format(TransactionDate,"yyyy-mm-dd")
& "#)"),"000") AS RowCounter,
TransactionDate, TransactionAmount
FROM Transactions
ORDER BY TransactionDate, TransactionID;

Ken Sheridan
Stafford, England

Steve wrote:
Hello Jon,

The table in your query must contain a unique index such as an autonumer
field. As an example in a report of customers, CustomerID would be a unique
field used in the query.
Example: To list all customers in the table, "TblCustomer" and auto number
the output rows, you would enter the following in a blank field of the
customers query:
RowNum: (Select Count (*) FROM [TblCustomer] as Temp
WHERE [Temp].[CustomerID] [TblCustomer].[CustomerID])+1

To get the format you want, you would then need to add another field to your
query:
CustomRowNum:Format([RowNum],"000")

Steve


I want to customize the numbering in a query. In other word, I want to make
customized numbering in a column in query but as formatted below:
[quoted text clipped - 5 lines]
etc
any help please?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1