Help with Bar Graphs

[mcmichael13]

Hey guys, I'm new to the site and I hope that i may both receive and give any help i can here.

On to the current issue I'm having:

I have created a new database in which the user will input RMA data with various forms. Now, I have been asked to have a "Report Generator" which takes only the RMA's that we have completed and seperates them in Location of RMA (we have several offices), and month completed, then sums the total value of the RMA from that location.

I am doing this with 2 locations right now and I have a query pulling the data based on a "MonthlyRMA" form that specifies the date range, and also the location. When the location combo box is left blank, a "Yes/No" messagebox prompts the user that a blank box will return records for both locations, "yes" proceeds, and "no" cancels the cmd.

This is all functioning and the query is made exactly how I wanted. The problem comes in when I am creating a bar graph from this query. I cannot get the "Locations" to be displayed as seperate series, instead the x-axis will show the month twice, once for each location. (Attached screenshot "AccessGraph").

I have been learning Access, VBA, and everything else while doing this but i would consider myself a quick learner. The logic of coding makes sense to me, I have just been learning the syntax of how to input the thought correctly.

If anyone has a way to get the bar graph to recognize that the locations are seperate series, that would be awesome.

Thanks so much,

Rob



Query code for Report:

SELECT (Format([DateReceived],"mmm"" '""yy")) AS [Month], Sum([RMA Monthly Query].TotalValue) AS RMAValue
FROM [RMA Monthly Query]
GROUP BY (Format([DateReceived],"mmm"" '""yy")), [RMA Monthly Query].Location, (Year([DateReceived])*12+Month([DateReceived])-1);

Query code for Report Generation based on MonthlyRMA form:

SELECT RMA.DateReceived, RMA.Location, RMA.TotalValue
FROM RMA
WHERE (((RMA.DateReceived) Between [Forms]![MonthlyRMA]![StartDate] And [Forms]![MonthlyRMA]![EndDate]) AND ((RMA.Location)=[Forms]![MonthlyRMA]![Location])) OR (((RMA.DateReceived) Between [Forms]![MonthlyRMA]![StartDate] And [Forms]![MonthlyRMA]![EndDate]) AND (([Forms]![MonthlyRMA]![Location]) Is Null))
ORDER BY RMA.DateReceived;

 

[mcmichael13]

no one? any help would be greatly appreciated

 

[Len Boorman]

Okay so charts in access can be a problem.

I do them without too much problem

This is the approach I use

1) You must end up with a single query that contains all the information for the chart including summations etc. No extra data. So you have X axis data, Y axis data and the Legend

2) Use the Form Wizard and follow the prompts.

Looking at your jpeg I would say that you have not correctly defined your legend. In your case the Legend should be Locations.

Your final query whilst quite straightforward and valid I would not use.

Query that Query with a simple select that selects only the data you want, sorted the way you want it.

Others would say your query is fine and I would not disagree but my experience says Keep It Simple. When you get to mixed Line/bar on multiple axis Simple is good.

HTH

L

 

[mcmichael13]

Thanks for the reply Ken,

now my query from which the graph is displayed is a simplified version of my table which contains all my information. the query is just the data i want in the graph. it contains location (for the series), total RMA (for the y), and Date (for the x).

When i use the graph wizard and select the 3 data sets for x,y, and series... then try to preview, nothing happens. i then finish the wizard and go into the properties tab. Under "Row Source" is:

TRANSFORM Sum([TotalValue]) AS [SumOfTotalValue] SELECT (Format([DateReceived],"MMM 'YY")) FROM [RMA Monthly Query] GROUP BY (Year([DateReceived])*12 + Month([DateReceived])-1),(Format([DateReceived],"MMM 'YY")) PIVOT [Location];

The query for this report is automatically set to "crosstab", to get the values for the.jpg i had above... i changed the report query to be "select" and set the values myself which is this code:

Query code for Report:

SELECT (Format([DateReceived],"mmm"" '""yy")) AS [Month], Sum([RMA Monthly Query].TotalValue) AS RMAValue
FROM [RMA Monthly Query]
GROUP BY (Format([DateReceived],"mmm"" '""yy")), [RMA Monthly Query].Location, (Year([DateReceived])*12+Month([DateReceived])-1);


I'm at a loss as to how to set the series to be location. I am probably missing something very simple. Thanks for your help

 

[Len Boorman]

I suggest that the query you use for the chart is a very simple select with no Formatting, Parameters or anything else so you end up with say

Date Value Location
2009/10 10 L1
2009/10 20 L2
2009/11 15 L1

etc
Now when you get the wizard up and select a Bar Chart you get three areas where you can set details
The X Axis should be Value and you may find that you have to set some form of Summation . Select any summation because in truth it does not matter if you have already done all the "tweaking required in earlier queries
Drag Locations if it is anywhere else except in the Legend area.

You mentioned crosstab. If necessary either query the crosstab and create a tempory table with a make table or whatever. My advice is get to a very simple query before attempting to chart


Len

 

[mcmichael13]

i'm not sure how i can simplify my query any more than it already is. the query is built from my "report selection" form, which allows the user to pick the date range and location for the RMA report. the query then selects the data from the master table that fits the criteria from the report selection form. there are two different locations available for selection, but if neither is chosen than results from both locations are given.

even when i follow the wizard and put "location" in the series area... it does not select location as the series.

i'm lost

Rob

 

[Len Boorman]

Based on the little sample of data in my post

Location is the Legend
Value is the Y Axis
Date is the X Axis

Not sure at all where problem lies

Post sample of you data from the query

L

 

[mcmichael13]

here are two screen shots of my data...

the first being a shot displaying the query made from the "report selection" form with the graph in the background...

the second is of the query builders for the query made from the form and also the query for the report

hope this helps diagnose.

Rob

 

[Len Boorman]

Okay had a look at your attachments
First the simple AccessSampleData.

You have multiple entries for location WA for February
I recommend that the query that you wish to use as the basis for the chart should be simple with all summations completed.
So continue to query this quesy until you have a single entry for January for each location.

I know that your query and the summation in the wizard appears to summarise correctly but as I said my recommendation is keep it very simple when it comes to the chart.

For Mar you will have only two lines. One for each location

I wonder also if you actually have 2 variables in the legend SumOf Total Value and Location.

Sum of Total Value should be the Y Axis

Len

 

[mcmichael13]

I got it working at the end of yesterday! I finally had enough with the "insert chart" and created another form to display in "PivotChart" mode. It's working quite nicely now. I came up with a couple more forms I need last night, like an "update RMA list" form that can alter the previously entered data.

Thanks for your help Len.

Rob