Improve company productivity with a Business Account.Sign Up

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

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
Asked:
babble324
  • 9
  • 6
  • 4
1 Solution
 
Rey Obrero (Capricorn1)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
 
babble324Author Commented:
I need this done for years worth of data.
0
 
Rey Obrero (Capricorn1)Commented:
how do you want the result look like?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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

This will then be charted on al ine graph using Access Graph Objects.
0
 
Rey Obrero (Capricorn1)Commented:
did you try the query posted above?
0
 
babble324Author Commented:
This will only work for 3 months. I need this for years' worth of data.
0
 
Rey Obrero (Capricorn1)Commented:
then expand the query.
0
 
aikimarkCommented:
@babble324

should a month's cumulative sum be for all prior months or just the prior months in that calendar year?
0
 
babble324Author 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
 
aikimarkCommented:
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]);

Open in new window

0
 
babble324Author 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
 
aikimarkCommented:
I guess you might try changing all these date values into the first of the month.

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

Open in new window


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

Open in new window

0
 
babble324Author 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));

Open in new window

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

Open in new window

0
 
babble324Author 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
 
aikimarkCommented:
please check that you corrected all instances of the typo.  If you still get the error message, then please post your SQL.
0
 
babble324Author 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);

Open in new window

0
 
aikimarkCommented:
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);

Open in new window

0
 
babble324Author 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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now