Is there a join that keeps non-matching rows from both tables?

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

Guest

I'd like to join queries and also keep all rows in each, regardless of
whether all rows in both queries match. The closest I've seen is an outer
join, but an outer join drops the non-matching rows from one of the queries.
How do I get around this problem?

Thanks,
Pat
 
Pat

If you have Query1 and Query2, and want to see all rows from each, take a
look at a UNION query (check Access HELP).

You'll need to copy the SQL statement of each together into a new query,
only in SQL view, looking something like:

SELECT ....
FROM ...
UNION
SELECT ...
FROM ...;

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Create two queries that are the same except one will be a Right Outer Join
and the other a Left Outer Join. Copy and paste the SQL statements from both
and join them together with a Union clause. It would look something like this:

SELECT Employees1.[Last Name] AS LastName1,
Employees1.[First Name] AS FirstName1,
Employees2.[Last Name] AS LastName2,
Employees2.[First Name] AS FirstName2
FROM Employees1 LEFT JOIN Employees2
ON Employees1.[Last Name] = Employees2.[Last Name]
UNION
SELECT Employees1.[Last Name] AS LastName1,
Employees1.[First Name] AS FirstName1,
Employees2.[Last Name] AS LastName2,
Employees2.[First Name] AS FirstName2
FROM Employees1 RIGHT JOIN Employees2
ON Employees1.[Last Name] = Employees2.[Last Name] ;

It could be slow and take a while to return records. Make sure to remove the
semi-colon ( ; ) from the first SQL statement.
 
Note: the UNION query will "drop" duplicates (if Q1 and Q2 return any
identical rows)

Note2: you MUST have the same number of fields in Q1 and Q2 to use the
UNION query

Note3: you do not have to have comparable data in those same number of
fields, but it's a really really good idea to -- otherwise, you get a data
set that contains apples and oranges in the same "field".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top