record counts and assigning a batch number

[Steven Cheng]

i have a transaction table where i export a subset of records and I would
like to have a unique number (going from 1 to 30) based upon the transaction
date. there are many transactions for each date but essentially it would
look like this:

Date Batch Number
3/1/2009 1
3/1/2009 1
3/3/2009 2
3/3/2009 2
3/4/2009 3
3/5/2009 4

i was trying to look this up as i had done this before but can remember how
to have this executed.

 

[Duane Hookom]

Try:

SELECT [Date], (SELECT Count(*) FROM tblTrans T WHERE T.[Date]
<tblTrans.[Date]) +1 As BatchNumber
FROM tblTrans;

 

[John Spencer]

Not sure this will work, but you can try

SELECT [Date],
(SELECT Count(*)
FROM (SELECT Distinct [Date]
FROM tblTrans as T
WHERE T.[Date]<TblTrans.[Date])) + 1 AS [Batch Number]
FROM tblTrans

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================