473,324 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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
Nov 12 '05 #1
2 2512
tom
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
jc****@tpg.com.au (Jeff Blee) wrote in message news:<38**************************@posting.google. com>...
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

Nov 12 '05 #2
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
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Sameer | last post by:
I am supplying you with Sample Data:- Initial Classcode SampleSize Average ------- ---------- ------------------------------- ADK SSC 22 3.6800000000000002 ADK TSC ...
1
by: mark.engelberg | last post by:
I am having trouble identifying the source of a memory leak in a Windows Python program. The basic gist is as follows: 1. Generate a directed graph (approx. 1000 nodes). 2. Write the graph to a...
14
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity....
2
by: Trev | last post by:
Hi, I need you suggestion and ideas as I am not a programmer by any means but I've been reading the articles on how to show a dynamic chart in asp web pages. I have to say it's brilliant and...
7
by: Bigs | last post by:
Ok, I have been working on a Linear Equation program that will draw a line on my graph. But, I am not sure how to set up the y=mx+b in Java to return the (x1,y1) and (x2, y2) using only the slope and...
7
by: Richard Cranium | last post by:
Trying to implement a simple rrd. Code below. When calling rrd_write, 8 successful times in a row, the 9th one causes this to happen: Program received signal EXC_BAD_ACCESS, Could not access...
2
by: sriniwas | last post by:
Hi Frnd's, m using prefuse visulation,it's have one display class and this class have one saveImage(outPutStream, String jpg,double size);. now graph is converting ia jpg image properly.now my...
0
by: APA | last post by:
I have a ASP.NET 20 application that is suffering some pretty poor performance. I'm using the Web Application Stress tool to run a script and I see some confusing performance data. As expected the...
2
bergy
by: bergy | last post by:
Background: We are collecting data and pushing it to MySQL at various intervals. Sometimes it may be every minute, other times every 15 minutes, and all ranges in between. This data is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.