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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote