How to compare two simple fields ?

[nrgz28]

Hello everybody. I'm new to Access, and I've been trying all day to
write a simple query that will compare a field in two different tables
and show me the differences. Here's what I got...

I've got two tables with ONE field in each, containing purchase order
numbers. I would like to run a query that will tell me when record
values from one table cannot be found in another and vice versa. Right
now one of the tables also has a "date" field attached. Later on I will
want to design a form where a user would put in a date, and run a query
that will the user if the records for that are found in one table but
not in the other, or vice versa. Any help on this ? :/ I appreciate
your time reading this.

 

[Van T. Dinh]

You need a full outer join in your Query. Unfortunately, Access (JET
database engine, actually) does not support full outer join.

To do this in Access, you need to use a Union Query with 2 partial outer
joins and the SQL String should look something like:

********
SELECT T1.DateField, "In Table1 but not Table2" AS SelNote
FROM Table1 AS T1 LEFT JOIN Table2 AS T2
ON T1.DateField = T2.DateField
WHERE T2.DateField Is Null

UNION

SELECT T2.DateField, "In Table2 but not Table1"
FROM Table1 AS T1 RIGHT JOIN Table2 AS T2
ON T1.DateField = T2.DateField
WHERE T1.DateField Is Null
********