By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

Cumulative monthly totals in charts

P: 20
Hi
I a trying to create a chart that reflects monthly cumulative totals from a query.
The query fields show sales opportunities forecasted invoice date (OppForInvDate), Opportunity Description and Estimated Value of the opportunity (OppEstVal). All I want is to chart, cumulatively for each month, the totals for the month?
Why is it so difficult, or is just me?

Mike
Mar 5 '07 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,365
You would normally do this from a report using a running sum.
Mar 5 '07 #2

P: 20
You would normally do this from a report using a running sum.
Okk ,it works, but how do I get the report to reflect monthly totals? Currently it gives me a cumulative total, but on a montyhly basis?
Mike
Mar 5 '07 #3

Rabbit
Expert Mod 10K+
P: 12,365
Set Month as a Group and set the running sum to Over Group.
Mar 5 '07 #4

P: 5
Replace your date field in the query with something like this...

OppForInvMonth: DateSerial(Year([OppForInvDate]), Month([OppForInvDate]),1) and Group by this field.

This will group all records by the the first day of the OppForInvDate. In your chart set the format to "mmm yy" or however you want to see it.

Mike
Mar 7 '07 #5

P: 20
Hi
Simple in Excel, appears difficult in Access. Posted a similar thread before, but cannot find it. All I want to do is create a chart that shows cumulative monthly totals.
I have pro(de)gressed to the point where I have a query in datasheet view that shows the month (rows) using 'Group by' property and the values in the columns/fields. Then created a form using "Running Sum over group" property to create the report. All very nice. Just what I wanted, thinking it would be easy to convert this report to a chart, but seeing that I cannot create a chart from a report(?). If this is true, then I suppose I should create the chart from the query, but cannot get a cumulative monthly result for the values over the successive months but only totals for the months. Must surely be easy.. (for those who know).
Please put me out of my misery.. (WIN2000 SP2 Acess 2003)
Mike
Mar 12 '07 #6

Rabbit
Expert Mod 10K+
P: 12,365
If you go to your control panel and list all your subscriptions you'll find it.

It's here: Cumulative monthly totals.
Mar 12 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Mike

Can you post the current SQL you are using for the report and say how you want the chart data cumulated.

Mary
Mar 12 '07 #8

P: 20
Mike

Can you post the current SQL you are using for the report and say how you want the chart data cumulated.

Mary
Hi Mary
below is a dump of the code from the query resulting in:
Records (rows) "Jan, Feb, March etc"
Fields (columns) totals for each month, field names sytarting with "Sum of..."

The report cumulatively adds the monthly totals. So the last month will shows the final total to date. This I have achieved successfully. All I need is to plot this report on a graph. When using the chart wizard, it does not allow me to construct a graph from the report?

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Format$([Sales Funnel Table Query].OppForcInvDate,'mmmm yyyy') AS [OppForcInvDate By Month], Sum([Sales Funnel Table Query].EstTotVal) AS [Sum Of EstTotVal], Sum([Sales Funnel Table Query].EstOppCCTV) AS [Sum of EstOppCCTV], Sum([Sales Funnel Table Query].EstOppCong) AS [Sum of EstOppCong], Sum([Sales Funnel Table Query].EstOppPA) AS [Sum of EstOppPA], Sum([Sales Funnel Table Query].EstOppFire) AS [Sum of EstOppFire], Sum([Sales Funnel Table Query].EstOpptInt) AS [Sum of EstOpptInt], Sum([Sales Funnel Table Query].EstOppiSys) AS [Sum of stOppiSys], Sum([Sales Funnel Table Query].EstOppSvc) AS [Sum of EstOppSvc], Sum([Sales Funnel Table Query].EstOppSocAl) AS [Sum of EstOppSocAl], Sum([Sales Funnel Table Query].EstOppOther) AS [Sum of EstOppOther]
  2. FROM [Sales Funnel Table Query]
  3. GROUP BY Format$([Sales Funnel Table Query].OppForcInvDate,'mmmm yyyy'), Year([Sales Funnel Table Query].OppForcInvDate)*12+DatePart('m',[Sales Funnel Table Query].OppForcInvDate)-1
  4. ORDER BY Year([Sales Funnel Table Query].OppForcInvDate)*12+DatePart('m',[Sales Funnel Table Query].OppForcInvDate)-1;
  5.  
Mar 13 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
I don't know if it will let you do this but it's worth a try ...

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum([Sales Funnel Table Query].EstTotVal) AS [Sum Of EstTotVal], Sum([Sales Funnel Table Query].EstOppCCTV) AS [Sum of EstOppCCTV], Sum([Sales Funnel Table Query].EstOppCong) AS [Sum of EstOppCong], Sum([Sales Funnel Table Query].EstOppPA) AS [Sum of EstOppPA], Sum([Sales Funnel Table Query].EstOppFire) AS [Sum of EstOppFire], Sum([Sales Funnel Table Query].EstOpptInt) AS [Sum of EstOpptInt], Sum([Sales Funnel Table Query].EstOppiSys) AS [Sum of stOppiSys], Sum([Sales Funnel Table Query].EstOppSvc) AS [Sum of EstOppSvc], Sum([Sales Funnel Table Query].EstOppSocAl) AS [Sum of EstOppSocAl], Sum([Sales Funnel Table Query].EstOppOther) AS [Sum of EstOppOther]
  2. SELECT DISTINCTROW Format$([Sales Funnel Table Query].OppForcInvDate,'mmmm yyyy')
  3. FROM [Sales Funnel Table Query]
  4. GROUP BY Format$([Sales Funnel Table Query].OppForcInvDate,'mmmm yyyy'), 
  5. Year([Sales Funnel Table Query].OppForcInvDate)*12+DatePart('m',[Sales Funnel Table Query].OppForcInvDate)-1
  6. ORDER BY Year([Sales Funnel Table Query].OppForcInvDate)*12+DatePart('m',[Sales Funnel Table Query].OppForcInvDate)-1
  7. PIVOT Format$([Sales Funnel Table Query].OppForcInvDate,'mmmm yyyy');
  8.  
Your problem is that your data is represented the wrong way around.

Mary
Mar 13 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.