Need a Query that shows cumulative totals

[Ed]

I have a table that lists Trouble Reports (TR). I need a query that will
show a cumulative total for each month over the year, like:

Date CountOf Opened TotalOpened
Feb '05 45 45
Mar '05 21 66
Apr '05 5 71

My table has the follwing fields:
dteDateOpen as date, sTRdesc as string

I can count the dteDateOpen for each month, but how do I get the total for
each month. This will feed a bar chart, so I do not want a 'report'.

ed

 

[Michel Walsh]

Hi,


A possible solution, based on the query, qs, that supplies the date
(monthYear) and the CountOfOpened:


SELECT a.monthYear,
LAST(a.CountOfOpened),
COUNT(*) As TotalOpened
FROM qs As a INNER JOIN qs As b
ON a.monthYear >= b.monthYear
GROUP BY a.monthYear




Hoping it may help,
Vanderghast, Access MVP

 

[Ed]

Michel,

This gave me a sequencial number not a cumulative total.
Also, the sequence it gave me was out of order since the a.monthYear is a
string.

ed

 

[Michel Walsh]

Hi,


You should try to use real date_time "as data" and FORMAT the data,
when required, for presentation ONLY. That would solve the actual problem of
wrong sequencing due to having literal.


You are right about the sequencing, should be SUM, not COUNT, as:

SELECT a.monthYear,
LAST(a.CountOfOpened),
SUM(b.CountOfOpened) As TotalOpened
FROM qs As a INNER JOIN qs As b
ON a.monthYear >= b.monthYear
GROUP BY a.monthYear


If you can't get the real date back, try to make a real date, such as adding
a 1 for the day, then




but if you have ' in the string, that won't work since


? CDate("1 " & "Dec '05")


would err, but

? CDate("1 " & "Dec 05" )

works fine.


If you get if fine, then

.... ON CDate("1 " & a.monthYear ) >= CDate("1 " & b.monthYear )


would do (but probably slower that if you would have got real date AND
indexes on them).


Hoping it may help,
Vanderghast, Access MVP

 

[Ed]

Thanks Michel, that did the job!

ed

Hi,


You should try to use real date_time "as data" and FORMAT the data,
when required, for presentation ONLY. That would solve the actual problem
of wrong sequencing due to having literal.


You are right about the sequencing, should be SUM, not COUNT, as:

SELECT a.monthYear,
LAST(a.CountOfOpened),
SUM(b.CountOfOpened) As TotalOpened
FROM qs As a INNER JOIN qs As b
ON a.monthYear >= b.monthYear
GROUP BY a.monthYear


If you can't get the real date back, try to make a real date, such as
adding a 1 for the day, then




but if you have ' in the string, that won't work since


? CDate("1 " & "Dec '05")


would err, but

? CDate("1 " & "Dec 05" )

works fine.


If you get if fine, then

... ON CDate("1 " & a.monthYear ) >= CDate("1 " & b.monthYear )


would do (but probably slower that if you would have got real date AND
indexes on them).


Hoping it may help,
Vanderghast, Access MVP