Running Count

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SHIPP - 29 May 2006 17:50 GMT

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.

Signature

M. Shipp

Reply to this Message

n3mesis - 29 May 2006 18:28 GMT

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
[/code]

so you get your query numberd.

even if you deletes an entry from the table, your queries will be
sequentially and without gaps continued

>I have this input data.
>1/1/06
[quoted text clipped - 8 lines]
>
>I am working in Access 97. Any help would be appreciated.

Reply to this Message

Ken Sheridan - 29 May 2006 18:35 GMT

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

> I have this input data.
> 1/1/06
[quoted text clipped - 8 lines]
>
> I am working in Access 97. Any help would be appreciated.

Reply to this Message

Ofer Cohen - 29 May 2006 18:50 GMT

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.

Signature

Good Luck
BS"D

> Try something like this:
>
[quoted text clipped - 20 lines]
> >
> > I am working in Access 97. Any help would be appreciated.

Reply to this Message

Ken Sheridan - 30 May 2006 12:17 GMT

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

> The problem with this, if the table has two records with the same date, it
> will display the same count.
[quoted text clipped - 6 lines]
>
> And sort the report by date.

Reply to this Message