Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2 outer joins (2003)

    I am not sure this is possible. I need to join two queries. I want to get what is alike from both tables but I want to get what isn't on the other query and vice versa. I want matching records and I want the ones that don't match from both tables. I need the joins where they are alike but I also need the records from both tables that are not alike. I hope I have explained this clearly.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 outer joins (2003)

    Access doesn't support two-way outer joins directly. You will have to create two queries for this:
    1) A union query that returns all values of the key field(s) from both tables. Let's say that you want to join the queries on a field named ID. The SQL for the union query would look like

    SELECT ID FROM QueryA
    UNION SELECT ID FROM QueryB

    2) A query based on the union query and on the two other queries.
    Do *not* join the two original queries in this new query.
    Join the union query to each of the two original queries on the key field(s), and make them outer joins (all records from the union query).

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 outer joins (2003)

    Right now I am joining the two queries with an outer join on the one query for 10 fields. How would the union query look for 10 fields. In the second step -A query based on the union query and on the two other queries. I am not sure how to do this. Is it in SQL or can I use the query grid? Thanks for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 outer joins (2003)

    An outer join on 10 fields? The mind boggles at the thought!

    You can include multiple fields in a union query:

    SELECT Field1, Field2, Field3, ..., Field10 FROM QueryA
    UNION SELECT Field1, Field2, Field3, ..., Field10 FROM QueryB

    You must create a union query in SQL view, but you can create the second query in design view.

Posting Permissions

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