Results 1 to 12 of 12
  1. #1
    Join Date
    May 2008
    Posts
    14

    Unanswered: Report line graph with several unrelated field lines?

    Hi again,

    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.

    I hope that makes sense...

    Thank you!

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    It sounds like WaterLevel and Tempreture are two different fields.

    Create a Goup By query.

    Date - Group By
    Iif([WaterLevel]="Low",1,0) - SUM
    Iif([Tempreture]="High",1,0) - SUM

    Use the graph wizard to create the graph. Make sure to select all three fields and the appropriate chart type.

  3. #3
    Join Date
    May 2008
    Posts
    14
    Thank you so much for your suggestion! I think I'm on the right track with it, but I'm afraid that I don't completely understand ... sorry I'm new to this!

    What is the "I" in front of the "if" in the expressions?

    Where do I write those expressions? Do I select the WaterLevel and Temperature fields for the query and then write those expressions in the Criteria boxes?

    Also, what does the "1,0" in the expressions mean?


    Thank you...
    Last edited by Slowlearner; 05-05-08 at 18:20.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by Slowlearner
    What is the "I" in front of the "if" in the expressions?
    Access has a weirdly named function IIF(). They say "Immediate IF" is the explanation, but I think it's just a tad confusing...

    Quote Originally Posted by Slowlearner
    Where do I write those expressions?
    In the supporting query.

    Quote Originally Posted by Slowlearner
    Do I select the WaterLevel and Temperature fields for the query and then write those expressions in the Criteria boxes?
    No, you put the expressions in columns in the query, they generate new columns which is the result of the calculations.

    Quote Originally Posted by Slowlearner
    Also, what does the "1,0" in the expressions mean?
    Translating to English... IF the WATERLEVEL is LOW then return a 1 OTHERWISE return a zero. You can then SUM or add all the results to get a count of how many 1s there are.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2008
    Posts
    14
    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...

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    [] are used to surround fields names.

    Post the query SQL.

  7. #7
    Join Date
    May 2008
    Posts
    14
    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:

    Year
    tInterview
    Group By
    Ascending

    The second block says:

    Expr1:IIf([WaterLevelsRivers]="Low*",1,0)
    tWeatherHuman
    sum

    The third block says:

    Expr2:IIf([TempLevel]="High*",1,0)
    tWeatherMonths
    sum

    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.

  8. #8
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    If your using a wildcard (*), use 'Like' instead of '='.
    Try the following:
    Expr1:IIf([tWeatherHuman]![WaterLevelsRivers]="Low*",1,0)

    sum


    Expr2:IIf([tWeatherMonths]![TempLevel]="High*",1,0)

    sum


    If this does not work, post back your table layouts for tInterview, tWeatherMonths and tWeatherHuman and an explanation of the relationship(s) between the three tables.

  9. #9
    Join Date
    May 2008
    Posts
    14
    I tried to use the new expression as you posted but I got the same syntax error message, only this time the message also added an extra bracket around the ! like this:

    ]]![[

    to give me the syntax error in query message:

    'Sum(tWeatherHuman.[IIf([tWeatherHuman]]![[WaterLevelsRivers]="Low*",1,0)])'

    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.

  10. #10
    Join Date
    May 2008
    Posts
    14
    Hi out there,

    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.

    Yaaaaaaaaayyyyyyyyyyyyyyyyyyyy!!!

    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!


  11. #11
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    This is the kind of stuff that I lose sleep over. If you have time, please post the SQL behind the two graphs.

  12. #12
    Join Date
    May 2008
    Posts
    14
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •