quandary on selecting certain records Pt.2

[javablood]

Clifford helped me out yesterday with an SQL for selecting all the STATION_ID
records for which there are zero values OR non zero values in the VOC field.
I implemented it and it works. I also needed the SQL for another data group
but now I am not getting the non zero values. Both SQLs are shown below and
I have gone over it so many times I am seeing double but still cannot see why
it is not working. Please help!

This one works:
SELECT A.STATION_ID, A.Volume_pump, A.Volum_cont, A.VOC,
IIf([VOC]=0,0,([VOC]*[Volum_cont])/100) AS NVOC
FROM tblFWAtmp_2 AS A
WHERE (((A.VOC)=0) AND ((Not Exists (select * from tblFWAtmp_2 as B where
B.STATION_ID = A.STATION_ID and B.VOC <> 0))=True)) OR ((A.VOC)<>0);

This one is not working:
SELECT A.STATION_ID, A.SAMPLE_DATE, A.VOC, A.Samp_Year
FROM tblPhytotmp AS A
WHERE (((A.VOC)=0) AND ((Not Exists (select * from tblPhytotmp as B where
B.STATION_ID = A.STATION_ID and B.VOC <> 0))=True)) OR ((A.VOC)<>0);

Thanks,

 

[javablood]

I guess I should state how it is not working. The records are either the
values greater than zero or zero. For example, if a STATION_ID has records
with values greater than zero and zeros, only those records with values
greater than zero are returned. If a STATION_ID has records with values only
zero, all the records are returned.

 

[javablood]

Well I feel silly! I needed to add a few more fields as follows to better
distinguish the records:

SELECT A.STATION_ID, A.SAMPLE_DATE, A.VOC, A.Samp_Year
FROM tblPhytotmp AS A
WHERE (((A.VOC)=0) AND ((Not Exists (select * from tblPhytotmp as B where
B.STATION_ID = A.STATION_ID and B.SAMPLE_DATE = A.SAMPLE_DATE and B.Samp_year
= A.Samp_Year and B.VOC <> 0))=True)) OR (((A.VOC)<>0));


--
javablood

I guess I should state how it is not working. The records are either the
values greater than zero or zero. For example, if a STATION_ID has records
with values greater than zero and zeros, only those records with values
greater than zero are returned. If a STATION_ID has records with values only
zero, all the records are returned.
--
javablood

Clifford helped me out yesterday with an SQL for selecting all the STATION_ID
records for which there are zero values OR non zero values in the VOC field.
I implemented it and it works. I also needed the SQL for another data group
but now I am not getting the non zero values. Both SQLs are shown below and
I have gone over it so many times I am seeing double but still cannot see why
it is not working. Please help!

This one works:
SELECT A.STATION_ID, A.Volume_pump, A.Volum_cont, A.VOC,
IIf([VOC]=0,0,([VOC]*[Volum_cont])/100) AS NVOC
FROM tblFWAtmp_2 AS A
WHERE (((A.VOC)=0) AND ((Not Exists (select * from tblFWAtmp_2 as B where
B.STATION_ID = A.STATION_ID and B.VOC <> 0))=True)) OR ((A.VOC)<>0);

This one is not working:
SELECT A.STATION_ID, A.SAMPLE_DATE, A.VOC, A.Samp_Year
FROM tblPhytotmp AS A
WHERE (((A.VOC)=0) AND ((Not Exists (select * from tblPhytotmp as B where
B.STATION_ID = A.STATION_ID and B.VOC <> 0))=True)) OR ((A.VOC)<>0);

Thanks,