Opposite results

[Stephen sjw_ost]

I have been trying to reverse a query but can not seem to figure it out.

I have 2 tables. Each table has 1 field, "agtid".
I made a query that uses an inner join to find all records from both tables
that are equal. Simple enough.

What I need is the opposite. I need the results to show me the records that
are not matching between the 2 tables. Anything in Table1 that is not in
Table2 I need displayed, then anything that is in Table2 but not in Table1.
This is probably 2 different queries but I can't seem to figure out how to
make access give me what seems like such a simple solution.

Any help is greatly appreciated.

 

[ghetto_banjo]

It will need to be 2 queries.


do a LEFT JOIN from table 1 to table 2 on your agtid field (so you
include all results in table 1 regardless of match in table 2).

then in the criteria for agtid in TABLE 2, say "Is Null" (forces no
match on your join)


this would give you all agtid in table 1 that is NOT in table 2.




switch her around for the 2nd query you need....

 

[ghetto_banjo]

basically your SQL should look like this when your query is setup:

SELECT table1.agtid
FROM table1 LEFT JOIN table2 ON table1.agtid = table2.agtid
WHERE table2.agtid Is Null;



and then switch the table1 and table 2 for your second query.

 

[Marshall Barton]

I have been trying to reverse a query but can not seem to figure it out.

I have 2 tables. Each table has 1 field, "agtid".
I made a query that uses an inner join to find all records from both tables
that are equal. Simple enough.

What I need is the opposite. I need the results to show me the records that
are not matching between the 2 tables. Anything in Table1 that is not in
Table2 I need displayed, then anything that is in Table2 but not in Table1.
This is probably 2 different queries but I can't seem to figure out how to
make access give me what seems like such a simple solution.

You can do it in one query by using UNION

SELECT agtid
FROM table1 LEFT JOIN table2
WHERE table2.agtid Is Null
UNION ALL
SELECT agtid
FROM table2
LEFT JOIN table1
WHERE table1.agtid Is Null

 

[Stephen sjw_ost]

I thought it would be simple. It works great.
Thanks for your help!