Need help to do data and averages in the one graph in A97.

I am hoping someone can help me. I am making a Access 97 app for a
person and have run up against a problem to do with MS Graph. There is
a table that has a number of data elements and a date field and
entries are entered each month.
A graph is required that has three of the data elements represented
basically on the y axis and time as months along the x axis. So for
each month there is a group of three columns representing the data
elements. Fairly straight forward and no problem so far. The graphs
shows 12 months of data and a 13th group of columns is required that
shows the averages of each of the data elements 12 months of data in
the graph.
Here is an example graph with just one data element
|
| X X
| X X X X X X X
|_______X___________________X_________X___________ ___X____________________
Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Avg

Basically this person had this existing already in Excel where it is
no problem to do what I have described. I have to convert it into an
Access 97 database, but in Access, the graph is driven off of a SQL
query. I have done the graph of the data over the 12 months alright
and have done a (1 column group) graph of the averages, but I am
tearing my hair out trying to combine the 2 queries together.(and I
haven't got enough hair left to spare any :-) )

I tried butting the 2 graphs up together but it doesn't look
particularly good, and if the data gets outside of the ranges that I
build in, the graphs won't line up very well. I need a graph, and
therefore an SQL query, that shows the data for each month and the
averges as I've described.
Can anyone suggest a way to sort this problem out? Thanking you in
advance,

Jeff Blee

re: Need help to do data and averages in the one graph in A97.

it depends how your SQL statement is setup, which i can't really tell
from the description, but i would use a Union query. if your current
query is setup up like:
Month Element1 Element2 Element3
jan 1 2 3
feb 5 6 7

copy the query and make the second query create averages. you will
need the month column as a where condition, but you don't want to show
it. the first column should be an expression that always returns the
"average" (or something like that)
Month Element1 Element2 Element3
average 3 4 5

put the two together with the "Union" statement. this has to be done
in the SQL view, there is no union functionality in the Query
Designer:

[query1]
UNION
[query2]

this will return one recordset that looks like:
Month Element1 Element2 Element3
jan 1 2 3
feb 5 6 7
average 3 4 5

from which a graph is simple, as you have done.

hope that helps.
tom


jcblee@tpg.com.au (Jeff Blee) wrote in message news:<38b0b6e1.0309010439.28baf383@posting.google. com>...[color=blue]
> I am hoping someone can help me. I am making a Access 97 app for a
> person and have run up against a problem to do with MS Graph. There is
> a table that has a number of data elements and a date field and
> entries are entered each month.
> A graph is required that has three of the data elements represented
> basically on the y axis and time as months along the x axis. So for
> each month there is a group of three columns representing the data
> elements. Fairly straight forward and no problem so far. The graphs
> shows 12 months of data and a 13th group of columns is required that
> shows the averages of each of the data elements 12 months of data in
> the graph.
> Here is an example graph with just one data element
> |
> | X X
> | X X X X X X X
> |_______X___________________X_________X___________ ___X____________________
> Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Avg
>
> Basically this person had this existing already in Excel where it is
> no problem to do what I have described. I have to convert it into an
> Access 97 database, but in Access, the graph is driven off of a SQL
> query. I have done the graph of the data over the 12 months alright
> and have done a (1 column group) graph of the averages, but I am
> tearing my hair out trying to combine the 2 queries together.(and I
> haven't got enough hair left to spare any :-) )
>
> I tried butting the 2 graphs up together but it doesn't look
> particularly good, and if the data gets outside of the ranges that I
> build in, the graphs won't line up very well. I need a graph, and
> therefore an SQL query, that shows the data for each month and the
> averges as I've described.
> Can anyone suggest a way to sort this problem out? Thanking you in
> advance,
>
> Jeff Blee[/color]

re: Need help to do data and averages in the one graph in A97.

Tom,
Thanks very much for your solution. That certainly worked. Both the
selected months and the average of those months are together in the
one query and therefore in the one graph. The only problem I have now
is that after the UNION, the order the entries are in got mucked up.
Instead of Jan, Feb, Mar, Apr ...... etc. it is now Apr, Avg, Dec,
Jan, Jun ... and so on. Here is the unioned query.

SELECT (Format([Problem Management].[Date],"mmm"" '""yy")) AS Month,
[Problem Management].S2S_Num_Problems AS Problems, [Problem
Management].S2S_Exceptions AS Exceptions, [Problem
Management].S2S_Avg_Res_Time AS [Avg Rest Time]
FROM [Problem Management]
WHERE (((DateDiff("d",Date(),([Problem Management].[Date])))>-396))
ORDER BY [Problem Management].Date
UNION
SELECT "Avg" AS Month, Avg(qryS2S.Problems) AS Problems,
Avg(qryS2S.Exceptions) AS Exceptions, Avg(qryS2S.[Avg Rest Time]) AS
[Avg Rest Time]
FROM qryS2S;

I had a bit of trouble with doing the second query straight from the
table, so I did the first query as a query and called it qryS2S. So
the second query just calls on another select query identical to the
first query. The datasheet view of that first query has the correct
ordering, but after the union the order follows the alphabetical month
names.
I've tried a few ideas but nothing so far is really suitable.
I'd appreaciate any further help if anyone has any ideas.

Thanking you,
Jeff Blee