Critera question

K

Kmarie

I need help figuring out the critera operator expression when you're trying
to exclude a piece of information.

For example, here is what my Tests field looks like:

Customer ID Tests
1 T993, T992, T991
2 T992
4 T995
5 T992, T993
6 T995, T992

I want to query all records that DON'T have test T993 listed. I've tried:

not "T993"
<> "T993"

And those don't seem to work.
 
A

Allen Browne

Try:
Not Like "*T993*"

Of course, the better solution would be to create a related table to hold
the test numbers.
 
J

John Spencer (MVP)

IF you have all those values in one field (Tests) then you need to look at a
redesign of your table structure.

HOWEVER, try

NOT LIKE "*T993*"

as the criteria against the TESTS field
 
K

Kmarie

John Spencer (MVP) said:
IF you have all those values in one field (Tests) then you need to look at a
redesign of your table structure.
Click to expand...

What kind of structure would you recommend? The "not like" thing works
great, by the way. Thanks.
 
J

John Spencer (MVP)

Basic structure

Customer Table
CustomerID
CustomerName
Address

Test Table
TestID
TestName
....

CustomerTests Table
CustomerID
TestID
TestDate

Then one way you could get your results would be:

Select CustomerName
From Customer
WHERE CustomerID NOT IN
(SELECT CustomerID
FROM CustomerTests
WHERE TestID <> "T993")
 
Top