Running Count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this input data.
1/1/06
2/3/06
3/6/06

I want the query to return


1/1/06 1
2/3/06 2
3/6/06 3

I am working in Access 97. Any help would be appreciated.
 
first create a new fieldname in your table named ID.
set Autonumber as datatyp

then copy this code into an empty field in your query

Code: 
INDEX: (Select Count (*) FROM [yourtable] as Temp WHERE [Temp].[ID] <
[yourtable].[ID])+1

so you get your query numberd.

even if you deletes an entry from the table, your queries will be
sequentially and without gaps continued
 
Try something like this:

SELECT YourDate,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.YourDate <= T1.YourDate) As Counter
FROM YourTable AS T1
ORDER BY YourDate;

Ken Sheridan
Stafford, England
 
The problem with this, if the table has two records with the same date, it
will display the same count.

You should use a report to display the data, in the report create an extra
text field that will display the counter.
Set the text field in the report properties to:
ControlSource = 1
Running Sum = Yes

And sort the report by date.
 
The desired output format might not necessarily be a report. If the date
column is not the key one would assume another column is, in which case its
simply a case of bringing the key into play:

SELECT YourDate, YourID,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.YourDate <= T1.YourDate
AND ( T2.YourID <= T1.YourID
OR T2.YourDate <> T1.YourDate)) AS Counter
FROM YourTable AS T1
ORDER BY YourDate, YourID;

This would return a result set such as:

YourDate YourID Counter
01/01/2006 1 1
01/01/2006 7 2
01/02/2006 2 3
01/02/2006 4 4
01/03/2006 3 5
01/03/2006 5 6
01/03/2006 9 7
01/04/2006 8 8
01/05/2006 6 9


Ken Sheridan
Stafford, England
 
Back
Top