asked on

MS Access 2003 query to select a record on multiple conditions

I need a query to select Field1, Field2, Field3, Field4 and Field5 from every record in Table1 *except* records that meet the following criteria:
- Records in table1 that have a "0" (zero) in Field3, Field4 and Field5.
AND
- also have the first 4 numbers of a 6 digit number in Table1.Field2 match the first 4 numbers of Table2.Field1

Both points (conditions) must be met for the record to be excluded from query results.

Rey Obrero (Capricorn1)

select *
from tablex
where (field3=0 or field4=0 or field5=0) and left(field2,4)=left(field1,4)

Rey Obrero (Capricorn1)

if you aare not getting the correct results, post sample data and the expected records you want to see

ASKER CERTIFIED SOLUTION

MNelson831

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

and I didn't see *except* <lol>

Muskie12

ASKER

capricorn1 - I *thought* your query looked funny but figured it was a case of it being brilliant in its simiplicity so I was trying to experiment with it anyway...

MNelson831 - what do you mean "2 tables Cap."? Also, I think since your query is selecting the records I want but using the logic of the records I want excluded, I *think* the "and"s in the first part of the WHERE need to be "or"s. I'm just playing with it now.

Muskie12

ASKER

Thanks MNelson831, a couple of slight mods (repl 'and's with 'or's and remove the 'not' gave me the records I was looking for.

SELECT Table1.Field1
FROM COA
WHERE (((COA.JAN)<>0)) OR (((COA.FEB)<>0)) OR (((COA.MAR)<>0)) OR (((Left([Account],4)) In (Select left(Field1,4) From Table2)));

MNelson831

My bad... I read the question as wanting to exclude records that met ALL conitions rather than records that met ANY condition