Setting up a query to graph two data series in access

[evbro]

Hi, I want to chart company goals in access. This was previously done in Excel. In Excel everything needs to be recreated on a yearly basis and I want to do something with a rolling year in Access. I have had a lot of "fun" trying to use the chart wizard...and OMG, I finally got something that displayed that was at least close to what I am looking for but it is still way off.

Here is what I am looking for: I want to graph by months the percent of target reached for a specific goal. So the X-axis would be months. The Y-Axis would be 0 to 100 % and the data by month would be two items; One being the [Goal] for the month and two, the [Actual Percent] of goal reached. I have the following Query that was produced by the Wizard:

PARAMETERS Forms!GoalsFrm.GoalID Long;
TRANSFORM Count(GoalChartqry.ActualPercent) AS CountOfActualPercent
SELECT (Format([GlDataDate],"mmm"" '""yy")) AS Expr1
FROM GoalChartqry
GROUP BY (Year([GlDataDate])*12+Month([GlDataDate])-1), (Format([GlDataDate],"mmm"" '""yy"))
PIVOT GoalChartqry.Goal;
 

Things that are not working are:
1) The Y-Axis is setup as 0 to 1.2 and needs to be 0 to 100%. I can format this as such but the data needs to show percent and not a count. I believe anyway.
2) The actual chart produced by the query has one line graph for the CountofAcceptedPercent. This is already in Percent in the GoalChartquery that pulls data from the table.
3) There is only one line graph and I want to show two. One for the [Goal] and one for the [Actual Percent]. I will format the graph later as line graph for the Goal and Column graph for the Accepted Percent.

I will also need to Show the chart by quarters after I get the months chart working. I am attaching an example of the Wizard Output from the above query and an example chart of what the end result should look similar to. The example has a third data series that is the sum of occurances. I would like to add this to the chart in the future.

[evbro]

Hello,

I have made what I think is some progress but I am still not obtaining the output I am looking for.

Here is what I did:
1) I changed the query TRANSFORM aggregate from COUNT to MAX. I believe this will work for now because the data is collected once per month. I tend to think AVG would be what I am looking for to aggregate the data but this currently produces an error.

TRANSFORM Count(GoalChartqry.AcceptedPercent) AS CountOfAcceptedPercent

Is now:

 
TRANSFORM MAX(GoalChartqry.AcceptedPercent) AS MAXOfAcceptedPercent

2) I also reformatted the chart to columns and formatted the vertical axis to show 50% to 100%

Now what I am looking for is adding another Line Graph to the chart to show the "Target %" goal. This will be another data series but how is this done in the SQL? Can it be done in this SQL code or do I have to make a UNION with another SQL query?

Also, the SQL was outputted by the wizard but is this correct? Do I need a TRANSFORM statement if I already have calculated % in the actual data?

Also, are the Pivot statment and groupings correct? I am a little green on how the SQL should be setup. Can anyone explain how the SQL should work and what I need for the second line graph?

I have attached the before and after output graphs. As a note, I am trying this charting with the built in Access charting and I am at the same time doing all things Identical to an unbound OLE Microsoft Graph. The Microsoft graph has a better looking output but also requires an added Microsoft Chart reference.

ANY HELP WITH HOW THE SQL WORKS WOULD BE APPRECIATED :)
- Teach me to fish and I will get my own food...