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
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