Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search results: ALL fields & records from 2 tables (2000)

    I should know how to do this, but haven't run into the need yet.

    I have two tables with some common fields (although I expect the solution I want would work with no 'linkable' fields). I want to create a query that will output all fields, and ALL records from both tables. For instance:

    Example:

    tblOne
    ======
    partnbr
    UPC
    Size
    Color

    tblTwo
    =======
    mfg
    Quantity
    ModelNbr
    UPC

    Want:
    =====
    partnbr UPC size Color mfg Quantity ModelNbr

    I thought a union query might be the way to go, but you must have the same number of fields in each table. The three options in the Join Properties form eliminate unmatched records from the tables, which I don't want to do.

    I want to use to search all records from two tables with different structure. I want to avoid creating a temporary table with appends to get what I want - would really prefer using a query.

    Hope this is clear. Any help would be appreciated.

    Thanks,

    Randy

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search results: ALL fields & records from 2 tables (2000)

    Do you mean a cross-product of the two tables? (record 1 from the first table together with all records from the second, record 2 from the first table together with all records from the second..., the total number of records being the product of the number of records from each)

    If so, create a query with the two tables with no line joining the two. You'll get all the combinations of records from both tables.

    If that's not what you mean, please elaborate.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search results: ALL fields & records from 2 tables (2000)

    You could create a table with the fields in that you want
    -partnbr UPC size Color mfg Quantity ModelNbr -
    and append both your original tables to it. then use that table as your source

    HTH

    Peter

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search results: ALL fields & records from 2 tables (2000)

    On second thoughts you could use 2 queries to add the missing fields then use a Union query to join them up, some thing like

    SELECT tblOne.partnbr, tblOne.UPC, tblOne.Size, tblOne.Color, "" AS mfg, "" AS Quantity, "" AS ModelNbr
    FROM tblOne;


    HTH

    Peter

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Search results: ALL fields & records from 2 tables (2000)

    One way to do this: Create 2 queries based on each table as follows. Add

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Search results: ALL fields & records from 2 tables (2000)

    Regarding your post.

    Since UPC is the common field between both tables, which of the following would you want?

    A. Select all fields from table 1 and only those fields from table 2 where the UPC is equal.
    B. Select all fields from table 2 and only those fields from table 1 where the UPC is equal.
    C. Select all records from both tables irregardless of what is equal between the two tables.

    For A and B, Create a query, add Table 1 and Table 2 and join the tables via the UPC code. Double click on the join line and select the relationship you want.

    For C, do as D. Martin suggested, although, this may give you a cartesian result and lots of excess data.

    If none of these meet your requirements, please re-elaborate on what you are trying to do.

    HTH
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search results: ALL fields & records from 2 tables (2000)

    Thanks for all of the replies. It looks like I shouldn't need to create any dummy table, just add the extra fields to the queries and create a union query on them. I appreciate all of the help.

    Randy

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search results: ALL fields & records from 2 tables (2000)

    You can add those expression fields in the select portions of the union query itself. The fields don't have to have the same name, only the same datatypes and in the same order in a union.
    Charlotte

Posting Permissions

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