Query to select only records with certain values

[path]

Sorry if this has been hashed over before, but I searched to see if I could find something similar...



I have a large database with over 1MM records. It is a database of reservation records, with 15 separate tables. I am attempting to do two things: find records containing ONLY certain field values, and none of the other possible values, and find records that do NOT contain those values.

Example: I need to find records that contain one of three values, and also contain one of 3 other values, but no additional values. The record needs to contain one of these 3 values - UO ADM, UO ADM FL or FLEX ADM, and it also needs to contain one of three other values - CW INCLUSION, TP TRANS or EPA. I don't want records that include any other values.

I'm fairly new to db queries, so I'm not sure if this is something simple, or complicated.

Any help is appreciated!

[pbaldy]

I assume those are two different fields? Try

SELECT...
FROM...
WHERE Field1 IN("UO ADM", "UO ADM FL", "FLEX ADM") AND Field2 IN("CW INCLUSION", "TP TRANS", "EPA")

[path]

Thank you for responding.

Unfortunately, each subset of values is in the same field. This is what is making it so difficult. Because I need one from the first subset, and one or more from the second subset - and no others from that field - it makes it very difficult to get just the records I need.

[pbaldy]

I would think you could use Like with wildcards or the InStr() function, and logic like:

(A OR B OR C) AND (D OR E OR F)

where each of the letters was a test using Like or InStr(). If you're still stuck, a sample db a few records and the expected result would help.