Looking for Help With Access - Writing a criteria that crossreferences 2 tables

[mschneider25]

Hello,
Let me start off by saying that i'm a real rookie with access. Ok now
that I got that out of the way - I am trying to create a criteria that
references 2 different tables with the same field. Both tables have
the field named PLNTCODE and I would like to run a criteria that says
if the PLNTCODE on one table exists on the other table then do not
return that particular entry. Is this possible and how can I do this?
Some more information below.

Thank you!


Field: PLNTCODE
Table1: US Current Electric Plants & Generators
Table2: US Planned Electric Plants & Generators

 

[KARL DEWEY]

This finds record in planned but not in current --
SELECT [US Planned Electric Plants & Generators].*
FROM [US Planned Electric Plants & Generators] LEFT JOIN [US Current
Electric Plants & Generators] ON [US Planned Electric Plants &
Generators].PLNTCODE = [US Current Electric Plants & Generators].PLNTCODE
WHERE ((([US Current Electric Plants & Generators].PLNTCODE) Is Null));

 

[John Spencer]

Do you want all records from table1 not in table2 and all records in
table2 not in table1?

SELECT Ta.PLNTCODE
FROM Table1 as Ta LEFT JOIN Table2 as Tb
ON Ta.PLNTCode = Tb.PLNTCODE
WHERE Tb.PLNTCode is Null

In the design view
-- Add both tables
-- Drag from PLNTCode to PLNTCode to set up a join
-- Double-click the join line
SELECT the option ALL in TableA and only in tableB
-- PUT PLNTCode from tableA in the list of fields
-- Put PLntCode from tableB in the list of fields
-- Set Criteria under TableB.PLNTCode to
IS NULL

Run the query. This will show you all the records in TableA that aren't
in tableB

Reverse the process to get all the records in TableB that are not in TableA

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================