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
9  14333 
You would normally do this from a report using a running sum.
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
Set Month as a Group and set the running sum to Over Group.
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
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
Mike
Can you post the current SQL you are using for the report and say how you want the chart data cumulated.
Mary
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;
-
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: paul |
last post by:
I have a query that takes monthly totals and rolls them up to get a
balance at a specific time code
eg:
dt bucket mon_ttl
--- ------ -------
199903 ...
|
by: tcread |
last post by:
OBJECTIVE
I'd like to be able to query the cumulative monthly returns of a given
stock based on the daily returns. I need to do this for 1000
securities over 5 years (60 months)
TABLES...
|
by: Victor |
last post by:
Hi There,
I have a query witch gives me the following result:
Period NumberOfItems
1 13
2 2
3 1
4 1
5 1
|
by: cefrancke |
last post by:
Are there any ways to speed up a Cumulative sum in a query or perhaps
another faster way to have a cumulative sum column (call a vba
function?).
For example, I want to sum up all values under...
|
by: Zlatko Matić |
last post by:
I have experienced some problems with total operations (sum, min, max, avg
etc) in pivot tables nad pivot charts in .mde.
In .mdb I can activate any totals operation. on both notebook and desktop...
|
by: lucky33 |
last post by:
I have a report that I am importing the data to an access database. I
know how to get the sum for the month to print my problem comes from
the Year to Date. What can I do to get the year to date...
|
by: cbellew |
last post by:
Hi guys, i'm looking to create a report with a table showing totals (running and cumulative) of education sessions attend by the staff at a hospital.
I'm trying to get the table to show something...
|
by: beacon |
last post by:
Hi everybody,
The title probably won't do this post justice, so I apologize ahead of time. Here's what I'm trying to accomplish...I have a report currently that shows totals for a date range that...
|
by: Sean Tech |
last post by:
Hello Everyone,
I have run into a problem with creating a line chart in access. My ultimate goal is to have the chart produce monthly totals for the salesmen and also show there quotas for the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
