Critera question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kmarie - 21 May 2005 03:40 GMT

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.

Reply to this Message

Allen Browne - 21 May 2005 04:30 GMT

Try:
Not Like "*T993*"

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I need help figuring out the critera operator expression when you're trying
> to exclude a piece of information.
[quoted text clipped - 14 lines]
>
> And those don't seem to work.

Reply to this Message

John Spencer (MVP) - 21 May 2005 04:38 GMT

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

> I need help figuring out the critera operator expression when you're trying
> to exclude a piece of information.
[quoted text clipped - 14 lines]
>
> And those don't seem to work.

Reply to this Message

Kmarie - 25 May 2005 05:35 GMT

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

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

Reply to this Message

John Spencer (MVP) - 26 May 2005 02:03 GMT

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")

> > IF you have all those values in one field (Tests) then you need to look at a
> > redesign of your table structure.
>
> What kind of structure would you recommend? The "not like" thing works
> great, by the way. Thanks.

Reply to this Message