Using a query to exclude a few records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello. I have a table in which i need to make a query that excludes records
if one of the columns is equal to one of two values. I have made the query,
and it does some strange things. The query was built in the query design
window.

SELECT tblSumRural.ZIPCODE, tblSumRural.CARRIER_ROUTE_ID, tblSumRural.AR,
tblSumRural.AB, tblSumRural.TOTAL, tblSumRural.CR, tblSumRural.PMonly
FROM tblSumRural
WHERE (((tblSumRural.CARRIER_ROUTE_ID)<>"G000" Or
(tblSumRural.CARRIER_ROUTE_ID)<>"G099") AND ((tblSumRural.PMonly)=1));

If i take out one of the CARRIER_ROUTE_ID conditions, it works great for the
one that is left in. For example, if i remove the condition for
CARRIER_ROUTE_ID<>"G099", it removes the records that have CARRIER_ROUTE_ID
equal to "G000", just like it should. But if both conditions are there, it
does not remove ANY recordrs and includes the ones with G000 and G099 in
them.

So how am i breaking the query by including two conditions? In either case,
the PMonly = 1 works, but that's not an exclusion, since it's looking for
records where PMonly equals 1.

Thanks for any help!!

Brian
 
Hello. I have a table in which i need to make a query that excludes records
if one of the columns is equal to one of two values. I have made the query,
and it does some strange things. The query was built in the query design
window.

SELECT tblSumRural.ZIPCODE, tblSumRural.CARRIER_ROUTE_ID, tblSumRural.AR,
tblSumRural.AB, tblSumRural.TOTAL, tblSumRural.CR, tblSumRural.PMonly
FROM tblSumRural
WHERE (((tblSumRural.CARRIER_ROUTE_ID)<>"G000" Or
(tblSumRural.CARRIER_ROUTE_ID)<>"G099") AND ((tblSumRural.PMonly)=1));

If i take out one of the CARRIER_ROUTE_ID conditions, it works great for the
one that is left in. For example, if i remove the condition for
CARRIER_ROUTE_ID<>"G099", it removes the records that have CARRIER_ROUTE_ID
equal to "G000", just like it should. But if both conditions are there, it
does not remove ANY recordrs and includes the ones with G000 and G099 in
them.
Click to expand...

Exactly. If the CARRIER_ROUTE_ID is equal to "G000" then you can say
with great certainty that it is NOT equal to "G099"; since it is not
equal to "G099" the second clause of your OR is TRUE. The OR logical
operator compares its two arguments and if either one is TRUE, it
returns TRUE - so it will find all records.

Either change the "or" to "and" - requiring that BOTH inequality
criteria must be true; or use the somewhat simpler syntax

WHERE tblSumRural.CARRIER_ROUTE_ID NOT IN("G000", "G099") AND
tblSumRural.PMOnly = 1

John W. Vinson[MVP]
 
Wow, i must have been tired yesterday. That makes perfect sence to me now :)
Thanks alot!!!

Brian
 
Back
Top