Critera question

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

Guest

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.
 
Try:
Not Like "*T993*"

Of course, the better solution would be to create a related table to hold
the test numbers.
 
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
 
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")
 
Back
Top