x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 728

# Query to get cumulative data

Hello,

I have a table with the following structure, this is in MS Access 2007.

TicketNumber  Date
2343               01/01/2011
1234               01/02/2011
3422               01/4/2011
3423               01/4/2011
7423               02/4/2011
3523               02/6/2011
3513               02/6/2011
3923               03/4/2011

I am able to create a crosstab that would add up the NUMBER of tickets per date; then I take generate a graph using the cross tab and I get a nice simple bar chart displaying tickets/month, so, Jan - 4, Feb - 3, March  - 1

My question now is, I would like to GRAPH a cumulative chart,

It will display, 4 tickets for Jan, 4+3 tickets for Feb, and 4+3+1 tickets for March.

Thanks,
0
babble324
• 9
• 6
• 4
1 Solution

Commented:
select sum(iif(month(date)<2,1,0)) as Jan,sum(iif(month(date)<3,1,0)) as Feb,sum(iif(month(date)<4,1,0)) as Mar
from table
0

Author Commented:
I need this done for years worth of data.
0

Commented:
how do you want the result look like?
0

Author Commented:
Jan --4
Feb --7
Mar --8
etc.
etc.

This will then be charted on al ine graph using Access Graph Objects.
0

Commented:
did you try the query posted above?
0

Author Commented:
This will only work for 3 months. I need this for years' worth of data.
0

Commented:
then expand the query.
0

Commented:
@babble324

should a month's cumulative sum be for all prior months or just the prior months in that calendar year?
0

Author Commented:
All months that are in the query. Also note that some months may not be there, I.e no tickets issued in that month. So I just want a tally of whatever was queried. Thank you so much for your help.
0

Commented:
I made a CumulativeSumProblem table and populated it with your posted data.

``````SELECT Year([TicketDate])*100+Month([TicketDate]) AS yyyymm, Count(CumulativeSumProblem.TicketNumber) AS CountOfTicketNumber,
(Select Count(*) From CumulativeSumProblem As CS Where Year(CS.[TicketDate])*100+Month(CS.[TicketDate]) <= Year(CumulativeSumProblem.[TicketDate])*100+Month(CumulativeSumProblem.[TicketDate]) ) As CumulativeSum
FROM CumulativeSumProblem
GROUP BY Year([TicketDate])*100+Month([TicketDate]);
``````
0

Author Commented:
yep, that's exactly what i want.

Now, under the yyyymm field in the query, is there a way it can be formatted differently? basically, i would like to take this data and feed it into a ms access chart, it's not recognizing "201103" as a date.

Thanks again,

0

Commented:
I guess you might try changing all these date values into the first of the month.

So this:
``````Year([TicketDate])*100+Month([TicketDate])
``````

becomes this
``````DateSerial(Year([TicketDate]), Month([TicketDate], 1)
``````
0

Author Commented:
I get wrong number of arguments.

``````SELECT DateSerial(Year([TicketDate]), Month([TicketDate], 1)) AS yyyymm, Count(CumulativeSumProblem.TicketNumber) AS CountOfTicketNumber,
(Select Count(*) From CumulativeSumProblem As CS Where Year(CS.[TicketDate])*100+Month(CS.[TicketDate]) <= Year(CumulativeSumProblem.[TicketDate])*100+Month(CumulativeSumProblem.[TicketDate]) ) As CumulativeSum
FROM CumulativeSumProblem
GROUP BY DateSerial(Year([TicketDate]), Month([TicketDate], 1));
``````
0

Commented:
it is a typo on my part.  there is a misplaced right parenthesis
``````DateSerial(Year([TicketDate]), Month([TicketDate]), 1)
``````
0

Author Commented:
It now says:

"You tried to execute a query that does not include the specified expression 'TicketDate' as part of an aggregate function"
0

Commented:
please check that you corrected all instances of the typo.  If you still get the error message, then please post your SQL.
0

Author Commented:
There you go.

``````SELECT DateSerial(Year([TicketDate]), Month([TicketDate]), 1) AS yyyymm, Count(CumulativeSumProblem.TicketNumber) AS CountOfTicketNumber,
(Select Count(*) From CumulativeSumProblem As CS Where Year(CS.[TicketDate])*100+Month(CS.[TicketDate]) <= Year(CumulativeSumProblem.[TicketDate])*100+Month(CumulativeSumProblem.[TicketDate]) ) As CumulativeSum
FROM CumulativeSumProblem
GROUP BY DateSerial(Year([TicketDate]), Month([TicketDate]), 1);
``````
0

Commented:
you didn't make enough corrections.
``````SELECT DateSerial(Year([TicketDate]), Month([TicketDate]), 1) AS yyyymm, Count(CumulativeSumProblem.TicketNumber) AS CountOfTicketNumber,
(Select Count(*) From CumulativeSumProblem As CS Where
DateSerial(Year(CS.[TicketDate]), Month(CS.[TicketDate]), 1) <= DateSerial(Year(CumulativeSumProblem.[TicketDate]), Month(CumulativeSumProblem.[TicketDate]), 1) ) As CumulativeSum
FROM CumulativeSumProblem
GROUP BY DateSerial(Year([TicketDate]), Month([TicketDate]), 1);
``````
0

Author Commented:
Thank you very much. It worked!

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.