Querying Any Part of Field Values 2

[debtworkshop]

I have 2 tables: Client and Prospects

My client table has a field [exclusions]. This defines the states my client will not take. I separate the state values as follows in the [exclusions] field:

SC,NC,GA

I have a query inner joined to match prospects with clients. I need the clients selected to not include any instance of the prospect's state [prospect].[state].

I've tried querying the [client].[exclusions] field with:

Not Like [prospect].[state]

This doesn't seem to fly. I would much appreciate help with this.

Thanks Tek-Tips!

MyDebtWorkshop

 

[traingamer]

what about

Not Like "*" & [prospect].[state] & "*"

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[lespaul]

For future reference a field should contain a SINGLE piece of information. In this example you are showing the THREE exclusions for this client in a single field. This is a non-normalized structure.

You have a many to many relationship between Clients and Exclusions (one client can have many exclusions one exclusion can be claimed by many clients). The way to handle a many to many relationship is with a junction table. In your case you should have a Client_Exclusion table that is structured like:

[tt]
ClientID Exclusion
1234 SC
1234 NC
1234 GA
7856 SC
9562 BT
[/tt]

Leslie

Have you met Hardy Heron?

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886