How to get data that's NOT in another Table?

A

ashanthenuka

k, I know that the heading is confusing, but let me try to explain it a
little. I have a table for plant data called "tblPlants" that contains
the fields plantCode, regionCode, companyCode and plantLocation. This
is kind of my main table. Then there is another table that's called
"tblPlantStatus" that contains the fields plantCode, Year and
plantStatus.

plantStatus is a number field that contains zeros for the plants that
are closed and "tblPlantStatus" only have closed plants in it. What I
want to do is to find all the plants that are not closed in a specific
year and I have tried the query below with no results -

SELECT DISTINCT tblPlants.plantCode, tblPlantStatus.plantStatus
FROM tblPlants LEFT JOIN tblPlantStatus ON tblPlants.plantCode =
tblPlantStatus.plantCode
WHERE (((tblPlantStatus.plantStatus)<>0));


I also tried to use the "DLookUp" and "Not" together in another query,
again with no results

SELECT DISTINCT tblPlants.plantCode FROM tblPlants
WHERE ((Not
(tblPlants.plantCode)=(DLookUp("plantCode","tblPlantStatus","plantCode
= " & [tblPlants]![plantCode] & " AND Year = 1999"))));


What should I do???....Is there any way to get data that is NOT in
another table?

Please help as I'm deadstruck here....

Thanks in advance
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Delete Query based in a table (different scenario) and fields combination. 5
Table design and information normalization 0
Auto-Populate Based on Other Fields 1
Plants Database w/Varieties 2
program to increase data by a percentage 9
Checking if data exists another table and, if yes, extracting a value 0
Recordset and acOLECreateLink problem 1
Aligning beyond "Joining" in query--forcing blank rows to appear 11

Top