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

    Union Query (2003)

    I have two tables - one called current and one called prior. I would like to get everything from the current and everything from the prior. I guess I want a union query. If I create 2 queries from the 2 tables and then do a union query like the following:
    Select Current.*
    From Current;
    Union Select Prior.*
    From Prior;

    Will this give me what I want. I want everything from Current and everything from Prior. I am not sure.

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

    Re: Union Query (2003)

    For a union query, the two (or more) constituting parts must have the same structure, i.e. the same types of fields in the same order.
    By default, a union query will suppress duplicates, i.e. if exactly the same record occurs in Current and in Prior, the union query will return only one. If you prefer to return both identical records, use UNION ALL instead of UNION.

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

    Re: Union Query (2003)

    I think what I want is all fields from both tables. I want them to match but I want to pick up records that aren't in one table but are in the other and vice versa. I know I have done this before but I can't remember how.

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

    Re: Union Query (2003)

    That might involve a union query but it's slightly different. Do you have a field or combination of fields on which you want to match the tables?

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

    Re: Union Query (2003)

    Yes - Client Number and Node Level

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

    Re: Union Query (2003)

    You can start by creating a union query

    SELECT [Client Number], [Node Level]
    FROM Current
    UNION SELECT [Client Number], [Node Level]
    FROM Prior

    This query will return all unique combinations of Client Number and Node Level from both tables. Save it as qryUnion.

    Next, create a query based on Current, qryUnion and Prior.
    There should be NO join lines between Current and Prior. If Access created them automatically, delete them.
    Join Current and qryUnion on Client Number vs Client Number and also on Node Level vs Node level.
    Double click each of the join lines in turn and select the option to include ALL records from qryUnion.
    Do the same for Prior and qryUnion.
    Add the Client Number and Node Level fields from qryUnion to the query grid (NOT those from Current and Prior).
    Add all other fields from Current and all other fields from Prior to the query grid, or those you need.

    This query should do what you want.

Posting Permissions

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