If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
How to customize the numbering in a query?
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 |
#3
|
|||
|
|||
How to customize the numbering in a query?
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? |
#4
|
|||
|
|||
How to customize the numbering in a query?
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 |
#5
|
|||
|
|||
How to customize the numbering in a query?
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 |
Thread Tools | |
Display Modes | |
|
|