Prev: Date as column Heading
Next: Exporting Query from Access 2007 to Excel 2007
From: julz on 30 Apr 2010 14:58
I need to number records in a table by a group. ie. Each account number may
have 1 - 5 records. I need them numbered 1, 2, 3, 4, 5, and then the next
account number would start over 1, 2, 3, and then the next etc.
I cannot think this one out. Any help is appreciated. Thanks
From: Duane Hookom on 30 Apr 2010 15:29
There is no order to records in an Access table so 1, 2, 3, 4, and 5 aren't
possible. If you want to number records in some order, you need to be able to
create an expression that identifies the order such as HireDate, alphabetical
by name, ID field value, or whatever.

If you still need assistance, please reply back with actual table and field
names, data types, and sample records with the desired "numbers".
--
Duane Hookom
Microsoft Access MVP


"julz" wrote:

> I need to number records in a table by a group. ie. Each account number may
> have 1 - 5 records. I need them numbered 1, 2, 3, 4, 5, and then the next
> account number would start over 1, 2, 3, and then the next etc.
> I cannot think this one out. Any help is appreciated. Thanks
From: KenSheridan via AccessMonster.com on 30 Apr 2010 18:22
As Duane said, you need some basis for ordering the rows first. Often a date
value is appropriate, but as there may be more than one row per date, you
then need a means of distinguishing between those rows; an autonumber column
such as TransactionID can be used for this. The rows per can then be
numbered in a query either by joining two instances of the table, e.g.

SELECT COUNT(*) AS RowCounter,
T1.Account, T1.TransactionDate, T1.TransactionAmount
FROM Transactions AS T1 INNER JOIN Transactions As T2
ON T2.Account = T1.Account
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)
GROUP BY T1.Account, T1.TransactionDate,
T1.TransactionAmount,T1.TransactionID;

Or by means of a subquery:

SELECT
(SELECT COUNT(*)
FROM Transactions AS T2
WHERE T2.Account = T1.Account
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) AS RowCounter,
T1.Account, T1.TransactionDate, T1.TransactionAmount
FROM Transactions AS T1
ORDER BY T1.Account, T1.TransactionDate, T1.TransactionID;

If the query needs to be updatable use the DCount function in place of the
subquery:

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

This assumes the Account column is a number data type, not text. Note the
formatting of the date values in the internationally unambiguous ISO format
for data notation.

Alternatively you can do it very simply in report grouped on Account by
adding an unbound text box to the detail section, with a ControlSource
property of =1 and a RunningSum property of 'over group'.

Ken Sheridan
Stafford, England

julz wrote:
>I need to number records in a table by a group. ie. Each account number may
>have 1 - 5 records. I need them numbered 1, 2, 3, 4, 5, and then the next
>account number would start over 1, 2, 3, and then the next etc.
>I cannot think this one out. Any help is appreciated. Thanks

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

 | 
Pages: 1
Prev: Date as column Heading
Next: Exporting Query from Access 2007 to Excel 2007