Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Unanswered: combining queries

    How to combine two queries so the combined query shows all data of both queries...
    Here an exemple of what I want

    query 1 gave as a result the following

    ID Year Quality
    1 2002 Good
    1 2003 Bad
    2 2002 Good
    3 2003 Regular

    query 2
    ID Year Quantity
    1 2002 100
    2 2002 200
    3 2002 300
    4 2002 400

    The combined query should give

    ID Year Quality Quantity
    1 2002 Good 100
    1 2003 Bad
    2 2002 Good 300
    3 2003 Regular
    4 2002 400


    I tried different kind of joints...but until now, no succes.
    Tnx for helping me out!
    Dave

  2. #2
    Join Date
    Sep 2003
    Location
    NJ
    Posts
    5

    Question.

    In the final result, how does the 300 relate to "good"? It seems that it should be 200.

    Except for that,

    SELECT Table2.id, IIf([table1].[year],[table1].[year],[table2].[year]) AS yr, IIf([quality],[quality],[quantity]) AS qual, IIf([table1].[year]=[table2].[year],IIf([quality],[quantity])) AS Quantity
    FROM Table1 RIGHT JOIN Table2 ON Table1.id = Table2.id
    WITH OWNERACCESS OPTION;

    should work.

  3. #3
    Join Date
    Apr 2004
    Posts
    12

    Unhappy

    TNX for your reply.
    you were right about the mix up of the 200 and 300.
    However, your solution only worked partly.
    If a combination of and ID and YEAR is in table1 and not in table2, it will show in the result. On the other hand, if there is a combination of ID and YEAR in table2, not existing in table1, than this will not be shown in the result.

    Since I believe this is kind of an action which is performed quiet a lot, doesnt Access has an easier solution??

    Tnx a lot,
    Dave

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dave_nys
    I tried different kind of joints...
    heh, so did i, back in the sixties... and i inhaled, too

    what you are looking for is a FULL OUTER JOIN

    not sure which versions of access might support it, i know access 97 doesn't

    but you can achieve the same results as follows:
    Code:
    select q1.ID
         , q1.Year
         , q1.Quality
         , q2.Quantity
      from query1 q1
    left outer
      join query2 q2
        on q1.ID   = q2.ID
       and q1.Year = q2.Year  
    union all
    select q2.ID
         , q2.Year
         , null
         , q2.Quantity
      from query1 q1
    right outer
      join query2 q2
        on q1.ID   = q2.ID
       and q1.Year = q2.Year  
     where q1.ID is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Posts
    12

    Smile great...it works!

    Tnx...that was it...worked perfectly!!

  6. #6
    Join Date
    Sep 2003
    Location
    NJ
    Posts
    5

    a-HA!

    So that's how you do a full outer join.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in databases that support LEFT OUTER and RIGHT OUTER but not FULL OUTER, yup, that's how you do it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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