Cumulate sums

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

Hello everyone,

I would like to build a query. This query should be able to cumulate the
value from line to line.

I have a table, and inside this table I have records. Some of these records
have a "closed" status and some of them have an "Open" Status. All of these
records have a date which is an "Open Date" (so the date when the record is
created).
Assuming I have twenty records and I started to enter data on january first.
From this date, I have entered new data, and have set sometimes the 'open'
status to 'close'

The thing I would like to see from my query is :

having one record per day telling me how many record on this particular day
I have "open" record and "Closed" record since the january first

=> Example of result

Open Close Date
1 0 1/1/04
2 0 2/1/04
2 1 3/1/04
5 2 4/1/04
5 5 5/1/04
6 5 6/1/04
....

35 29 9/2/04

So, on january 5th, the result mean "From january first till today, you have
opened 5 records and closed 5 records" and does NOT means "On january 5th,
you have opened five records and opened five records"

Is anybody can help me on this ?


Thanks very much in advance

Don
 
Try this, it runs slower than a snail, but it does get
there...

first query (Q1).
SELECT Count([AnyField]) AS [StOpen], [DateField] FROM
YourTable
GROUP BY [DateField]
Where Status = 'Open';

second query (Q2).
SELECT Count([AnyField]) AS [StClosed], [DateField] FROM
YourTable
GROUP BY [DateField]
Where Status = 'Closed';

third query (Q3)
SELECT [DateField] FROM YourTable
GROUP BY [DateField];

fourth query (Q4)
SELECT
[Datefield],
DSum("[StOpen]","Q1","DateField <=#" &
format$([DateField], "mmm dd yyyy" & "#") AS RunOpen,
DSum("[StClosed]","Q2","DateField <=#" &
format$([DateField], "mmm dd yyyy" & "#") AS RunClosed,
FROM Q3;

If you want it to go faster, you will probably need to
resort to code.
 
Thanks Chris, it's working :-)



Chris said:
Try this, it runs slower than a snail, but it does get
there...

first query (Q1).
SELECT Count([AnyField]) AS [StOpen], [DateField] FROM
YourTable
GROUP BY [DateField]
Where Status = 'Open';

second query (Q2).
SELECT Count([AnyField]) AS [StClosed], [DateField] FROM
YourTable
GROUP BY [DateField]
Where Status = 'Closed';

third query (Q3)
SELECT [DateField] FROM YourTable
GROUP BY [DateField];

fourth query (Q4)
SELECT
[Datefield],
DSum("[StOpen]","Q1","DateField <=#" &
format$([DateField], "mmm dd yyyy" & "#") AS RunOpen,
DSum("[StClosed]","Q2","DateField <=#" &
format$([DateField], "mmm dd yyyy" & "#") AS RunClosed,
FROM Q3;

If you want it to go faster, you will probably need to
resort to code.
-----Original Message-----
Hello everyone,

I would like to build a query. This query should be able to cumulate the
value from line to line.

I have a table, and inside this table I have records. Some of these records
have a "closed" status and some of them have an "Open" Status. All of these
records have a date which is an "Open Date" (so the date when the record is
created).
Assuming I have twenty records and I started to enter data on january first.
From this date, I have entered new data, and have set sometimes the 'open'
status to 'close'

The thing I would like to see from my query is :

having one record per day telling me how many record on this particular day
I have "open" record and "Closed" record since the january first

=> Example of result

Open Close Date
1 0 1/1/04
2 0 2/1/04
2 1 3/1/04
5 2 4/1/04
5 5 5/1/04
6 5 6/1/04
....

35 29 9/2/04

So, on january 5th, the result mean "From january first till today, you have
opened 5 records and closed 5 records" and does NOT means "On january 5th,
you have opened five records and opened five records"

Is anybody can help me on this ?


Thanks very much in advance

Don



.
Click to expand...
Click to expand...
 
Back
Top