Unique data from Union Query

[Guest]

Hi
I need to write a UNION query (I think) to get info from 2 tables: Table1
and Table2, with the following fields:
Table1: Table1ID, Route, Step
Table2: Table2ID, Route, Step

What I need is ALL info from Table1 and only those from Table 2 where field
Route is the same. In addition, if Step already exist in Table1, I don't want
to show it again from Table2. Can this be achieved? I hope what I described
makes sense.
Thank you in advance.

 

[John Vinson]

Hi
I need to write a UNION query (I think) to get info from 2 tables: Table1
and Table2, with the following fields:
Table1: Table1ID, Route, Step
Table2: Table2ID, Route, Step

What I need is ALL info from Table1 and only those from Table 2 where field
Route is the same. In addition, if Step already exist in Table1, I don't want
to show it again from Table2. Can this be achieved? I hope what I described
makes sense.
Thank you in advance.

Try a UNION query using an "unmatched" query:

SELECT Table1.Table1ID, Table1.Route, Table1.Step
FROM Table1
UNION
SELECT Table2.Table2ID, Table2.Route, Table2.Step
FROM Table2 RIGHT JOIN Table1
ON Table2.Step = Table1.Step
WHERE Table1.Step IS NULL;

This will exclude any records from Table2 if their value of STEP
exists in ANY record in Table1 - if you mean by "STEP already exists"
that "STEP already exists for matching values of ID" you'll need to
also join by ID.

John W. Vinson[MVP]