Return all records from make-table query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to return all the records from two different tables with an
intermediate "join" connector line on a common field [part number] to both
tables. I realize some of my records will repeat but I want to proceed
anyway. Is there a way to return all the records from my first table and
second table in a make-table query? My first table has [part number] records
the second table doesn't have. My second table has many similar [part number]
records as my first table but also has additional records the first table
does not have.

Any point in the right direction would be greatly appreciated.
 
I am trying to return all the records from two different tables with an
intermediate "join" connector line on a common field [part number] to both
tables. I realize some of my records will repeat but I want to proceed
anyway. Is there a way to return all the records from my first table and
second table in a make-table query? My first table has [part number] records
the second table doesn't have. My second table has many similar [part number]
records as my first table but also has additional records the first table
does not have.

Any point in the right direction would be greatly appreciated.
Click to expand...

What you're asking for is called a "Full Outer Join", a query type
which is not supported in Access. You can get the same result using a
query based on two other queries.

First create a query joining TableA to TableB by [part number]; select
the join line and choose option 2 - "show all records in TableA and
matching records in TableB". Select all the fields you want to use.
Save this query.

Then create another query just like this, but using Option 3 - "show
all records in TableB and matching records in TableA".

Finally create a new query in the SQL window:

SELECT * FROM firstquery
UNION
SELECT * FROM secondquery

The UNION operator will string together the two sets of data, and
remove duplicates (in this case, all the PartNumbers which exist in
both tables would be duplicates).

You can then base a MakeTable query on the UNION query.

John W. Vinson[MVP]
 
Back
Top