Cumulative monthly totals in charts

[MikeSA]

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

[Rabbit]

You would normally do this from a report using a running sum.

[MikeSA]

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

[Rabbit]

Set Month as a Group and set the running sum to Over Group.

[wvmbark]

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

[MikeSA]

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

[Rabbit]

If you go to your control panel and list all your subscriptions you'll find it.

It's here: Cumulative monthly totals.

[MMcCarthy]

Mike

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

Mary

[MikeSA]

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?

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]
FROM [Sales Funnel Table Query]
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
ORDER BY Year([Sales Funnel Table Query].OppForcInvDate)*12+DatePart('m',[Sales Funnel Table Query].OppForcInvDate)-1;
 

[MMcCarthy]

I don't know if it will let you do this but it's worth a try ...

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]
SELECT DISTINCTROW Format$([Sales Funnel Table Query].OppForcInvDate,'mmmm yyyy')
FROM [Sales Funnel Table Query]
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
ORDER BY Year([Sales Funnel Table Query].OppForcInvDate)*12+DatePart('m',[Sales Funnel Table Query].OppForcInvDate)-1
PIVOT Format$([Sales Funnel Table Query].OppForcInvDate,'mmmm yyyy');
 

Your problem is that your data is represented the wrong way around.

Mary