Unanswered: Report line graph with several unrelated field lines?
I'm having one other problem with Access, if anyone can help me I would soooooooooo appreciate it, I'm so frustrated! I don't have anyone else to help me around here...
I'm using Access '97. My scenario is: I've got survey results for people who have noticed climate changes and effects. I need to make a report with a line graph that shows different fields on the same graph. My x-axis is "date by year", and my y-axis should be "# of people" (but I don't have a separate field for this). The different fields I need to use are "water levels" (which could be high, average, or low) and "temperature" (which could be warmer than most years, or colder than most years).
I am able to make a chart using one field no problem, for example if I make a chart for water levels. To do this I make a query with "date by year" and "water levels" specifying the criteria for "water levels" as either high, average or low. When I create the chart in a report, Access automatically calculates the y-axis for me as a "count". I don't have to specify anything anywhere for it.
But what do I have to do to make the same graph, but only add another field that is independent from the field already in my chart? I would be using the same x and y axis after all. The results would show me the correlation of what people noticed for changing water levels and temperatures each year over time. For example, I would like a chart that plots one line for "water levels" with a "low criteria" to represent low water levels; and one line for "temperature" with a "warmer than most years" criteria to represent high temperatures.
Thank you for the suggestion and for clarifying, rogue and StarTrekker!
So I did that, but when I press the "!" button to see it, I get an error message saying: Syntax error in query expression 'Sum(tWeatherHuman.[IIf([WaterLevelsRivers]="Low*",1,0)])'
I don't know how to fix this...
Is there just some way I could possibly copy the data from the field of one chart and add it to the data table of the other chart? They're using the same x and y axes after all. When I tried this it kind of showed up correctly in Design View (the data table showed another field, and the legend showed another line) but it didn't change anything in Print Preview...
The program won't let me keep the query with this syntax error, I can't save it, copy it, or look at it's SQL view, it just gives me the syntax error message... so unfortunately I can't show you directly...
But I can try and explain what my query looks like...
I've got three tables in the query. One "tInterview" where I get the "Year" field from. One "tWeatherHuman" where I get the "WaterLevels" field from. And one "tWeatherMonths" where I get the "TempLevel" field from. The tables have relationships by their "AutoInterviewID" field.
The first block in the query says:
The second block says:
The third block says:
And the program won't run this query, or let me save it, or let me look at the SQL. I just get the syntax error message I posted already.
So I tried to use Like instead of = and I got the same syntax error messages, both in the new expression you suggested and in the original expression.
I tried to export and copy/paste the tables as separate files on my computer so I could attach them to this post but the computer won't let me do either.
I tried to make a new db and copy/paste the tables into it so I could attach the whole db to this post but the computer won't let me.
I'm not sure of how else to describe the tables/queries/relationships other than what I've already posted... I guess I'll just have to wait until the lady who created this db gets back... whenever that will be.
My computer just isn't co-operating with me
Thanks for trying to help me though! I really appreciate it.
Ok! So I've been plugging away at this non-stop (couldn't help myself... guess I just don't know when to give up!) and I figured out a way to do it! I don't think it's the 'proper' way, but hey, whatever works, right?
What I did was make two separate graphs, one for my "WaterLevels" field and one for my "Temperature" field. Then I just copied and pasted one on top of the other, making sure that it's background was transparent (right click on graph- properties- format-back style- transparent). Kind of your fast-food/instant/microwaveable way of doing it.
I had to make sure the titles and the y-axes scales displayed the same way in Print Preview (I've noticed that things in Design View don't display the same way as in Print Preview!), so that they match up perfectly when pasted together. Also, I had to make sure that the points on my lines are very small- size 2 or 3- because otherwise they get in the way of each other and the program will mess up the x-axes so they won't line up properly.
Then it was just a matter of manipulating the legend boxes so that they could both be displayed at the same time without one overlapping the other. The key was to remove the border on both legend boxes and to move both legend boxes. Moving one will change how things are displayed on the graph so the graphs won't match up properly. But moving both will change the display on both graphs in the same way so they will match up.
I hope this thread may help other people out who are struggling with the same problem, since it seems to be a difficult problem out there...
Thank you so much rogue for your help! I really appreciate you taking so much time to try and help out a stranger
And thanks to StarTrekker too for the clarifications- there's nobody around here to help me understand things with this program!
Um... I know this might sound kinda stupid, but sorry I'm not sure what you mean... I really am just learning my way around Access
For the Spring Temp query that I built the graph on I have this:
SELECT tInterview.InterviewYear, tWeatherMonths.TempLevel
FROM (tInterview INNER JOIN tWeatherHuman ON tInterview.AutoInterviewID = tWeatherHuman.AutoInterviewID) INNER JOIN tWeatherMonths ON tInterview.AutoInterviewID = tWeatherMonths.AutoInterviewID
WHERE (((tWeatherMonths.Month) Like "*Sp*") AND ((tWeatherMonths.TempLevel) Like "Warm*"))
ORDER BY tInterview.InterviewYear;
And for the Water Levels query that I built the graph on I have this:
SELECT tInterview.InterviewYear, tWeatherHuman.WaterLevelsRivers
FROM tInterview INNER JOIN tWeatherHuman ON tInterview.AutoInterviewID = tWeatherHuman.AutoInterviewID
WHERE (((tWeatherHuman.WaterLevelsRivers) Like "*Low*"))
ORDER BY tInterview.InterviewYear;
But the two separate graphs I made are just regular graphs out of Chart Wizard. I just copied one and pasted on top of the other. This is what the Row Source says for the Spring Temp graph which is pasted on top of the Water Levels graph:
TRANSFORM Count(*) AS [Count] SELECT [InterviewYear] FROM [Spring Temp Graph Query] GROUP BY [InterviewYear] PIVOT [TempLevel];
And the Water Levels graph has the exact same thing for it's Row Source, only with the Water Levels data in it.
I know it's probably cheating, but... it's the only way I can get what I need right now...