Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2004
    Posts
    26

    Unanswered: 3rd Query giving Results of 2 other Queries

    Hello,

    I am trying to get a query working in Access, but am having problems. I wonder if someone can have a look at it, and let me know where I am going wrong ?

    Firstly, I run 1 grouped query based on a single table of records with a date criteria of yesterday's date.
    I group by the area, then provide a count of records against each area, with an average duration of each record.

    So my query looks like :

    Area - Count - Duration
    ABCD - 35 - 9.02
    EFGH - 22 - 7.45
    and so on.

    I then have the exact same query but for the day before yesterday.

    Now I want to combine the two to have something like :

    Area - Count (Today) - Duration (Today) - Count (Yesterday) - Duration (Yesterday)
    ABCD - 35 - 9.02 - 42 - 8.43
    and so on.

    So I have created a 3rd query and pulled in the two original queries, then added the fields from both.

    with a Number 3 Join, it seems to work fine to an extent. What I mean by that is, that it provides me with all records that appear on both days.

    However, if something appears today, but not yesterday (or vice versa), then the records doesn't show in the query.

    I hope someone can help.

    Thank you in advance.

    J.

  2. #2
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    the queries, as you've designed them, are working fine. You obviously wouldn't get any change in the records until you looked at the results tomorrow i.e.

    Query1:Yesterday
    Query2: Day before yesterday.

    See the pattern?

    Now, if you did one for today and one for yesterday, then you would see some changes happening.

  3. #3
    Join Date
    Jun 2004
    Posts
    26
    Apologies, my last sentance should have read :

    [However, if something appears yesterdat, but not the day before yesterday (or vice versa), then the records doesn't show in the query.]

    Does this help clarify what I need ?

    Thanks.

    J.

  4. #4
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Sure does.

    How are you accessing the queries? Are you just opening them from the database window?

  5. #5
    Join Date
    Mar 2005
    Posts
    14
    Your best bet is to use a union (SQL specific) query to join the other two query results together. I'll get back to you with an example shortly.

  6. #6
    Join Date
    Jun 2004
    Posts
    26
    Thanks.

    I am just opening them from the DB window.

    J.

  7. #7
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    sounds a bit silly, but have you tried closing the queries, making the modifications then opened all three queries again in the correct order?

  8. #8
    Join Date
    Mar 2005
    Posts
    14
    Are both days records stored in the same table with you querying by date?

  9. #9
    Join Date
    Jun 2004
    Posts
    26
    hi Baldy,

    Yes they are in one table.

  10. #10
    Join Date
    Mar 2005
    Posts
    14

    Wink

    Hello Again.

    This one is causing me more of a headache than I thought. The best I can come up with so far is a union query with the following sql:

    SELECT Area, null, null, CountOfArea, SumOfDuration From Query1
    UNION SELECT Area,CountOfArea, SumOfDuration, null, null From Query2;

    CountOfArea and SumOfDuration are the two names used to display the fields in each query. Unfortunately, you end up with a separate record for each area and day.

    With a little more info I can write a module for you that will use a recordset to put the results into a new table in the format you require.
    When queries get to this level of complexity it is often necessary to use modules.

    Please attach a copy of your database for me to look at. Thanks.

  11. #11
    Join Date
    Jun 2004
    Posts
    26
    Hi Baldy,

    Thanks for your offer of help, but I have managed to get round it by having an extra query which provides all areas logged over the past 2 days, then adding this to the query and having 2 Left Joins to my tables.

    It all works fine now.

    However, I have run into another problem, which judging from your knowledge so far, I think would be quite easy for you.

    I am now moving a step forward now, and doing calculations based on the query, and one of them involves division. It works fine until divding by 0, then it provides #Error in the field.

    Can I do an IIF statement to remove this #Error, and replace it with somethign like "N/A"?

    Thanks,

    J.

  12. #12
    Join Date
    Mar 2005
    Posts
    14
    It would be better not to cause the error and an IIf might be the answer.

    Can you give me an example of the data and query you are using.

    Cheers.

  13. #13
    Join Date
    Jun 2004
    Posts
    26
    No problem.

    I've attached a sheet with an example of the data on.
    It also has my 2 calcluations on.

    If you need to know anything else, please let me know.

    J.
    Attached Files Attached Files

  14. #14
    Join Date
    Feb 2005
    Posts
    333
    Change: iif([yesterday] = 0; some default value,[difference]/[yesterday]*100)

  15. #15
    Join Date
    Jun 2004
    Posts
    26
    Cheers Campster - That worked a treat.

    J

Posting Permissions

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