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 14352 Rabbit 12,516
Recognized Expert Moderator MVP
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
Rabbit 12,516
Recognized Expert Moderator MVP
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
Rabbit 12,516
Recognized Expert Moderator MVP
If you go to your control panel and list all your subscriptions you'll find it.
It's here: Cumulative monthly totals.
MMcCarthy 14,534
Recognized Expert Moderator MVP
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;
-
MMcCarthy 14,534
Recognized Expert Moderator MVP
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 0192 -172527
199906 0192 546707
199909 0192 -278684
199910 0192 1100139
|
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
Returns ~ with the following fields:
1. ID
2. Ticker
3. From (prior date end)
|
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 'Amount', and also have
a field call 'Cumulative' and have the sum grow with each record. Using
"MyTable", the query results are...
ID or Date ...
|
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
PC. When I create .mde on the notebook, I can use totals on laptop, but not
on the desktop PC. I just can't switch on any total. There is no total...
| |
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 figures to
print on the same page as the month to date?
Thanks
|
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 like this:
Month/Year | No. of sessions in this month | Cumulative total
Jan 07 2 ...
|
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 the user enters through a form. Typically, the user will enter a fiscal quarter for the date range (max of 92 days), as that's our normal reporting...
|
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 month. The salesmen data is in 1 table and the quotas are in the other. In the salesmen data table I have Sale date, Won price, Salesmen and other misc...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |