469,306 Members | 1,881 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

Cumulative monthly totals in charts

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
9 13678
Rabbit
12,516 Expert Mod 8TB
You would normally do this from a report using a running sum.
Mar 5 '07 #2
MikeSA
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
12,516 Expert Mod 8TB
Set Month as a Group and set the running sum to Over Group.
Mar 5 '07 #4
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
MikeSA
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
12,516 Expert Mod 8TB
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
14,534 Expert Mod 8TB
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
MikeSA
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
14,534 Expert Mod 8TB
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.

Similar topics

3 posts views Thread by paul | last post: by
reply views Thread by tcread | last post: by
1 post views Thread by Victor | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.