Unique - satisfies 1 of criteria, etc.

[misscrf]

I have a table of customers. This table has their name, an address 1 field, a check payable to field, and more. To preface this question, we got the data from other people and just have to get the answers out of the data for the time being.

I am trying to query the following:

1) All records where the "check payable to" field does not = the customer's name.

2) All records where the "check payable to" field does not = the customer's name&" "& the "address 1" field

3) All records where the "check payable to" field contains "*&*"

4) All records where the "check payable to" field contains "*and/or*"

5) All records where the "check payable to" field contains "* and *"

Some things I have found. If the customers name doesnt match the check payable, but the customer's name and their address 1 field matches, I don't need them.

Also, number 5 is because a lot of people have "and" in their name. (Anderson)

I would so appreciate any help. I have tried this many ways without much of any luck!

Thanks,

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Golom]

Something like this?

WHERE [CheckPayableTo] NOT LIKE [CustName] & "*"
  AND [CheckPayableTo] NOT LIKE '*&*'
  AND [CheckPayableTo] NOT LIKE '*and/or*'
  AND [CheckPayableTo] NOT LIKE ' *and* '

of course this still doesn't deal with things like
[tt]
[CustName] ---> Joe Blow
[CheckPayableTo] ---> J. Blow
[/tt]
but it's a start.

 

[Golom]

Sorry. That was dumb

WHERE [CheckPayableTo] NOT LIKE [CustName] & "*"
  OR  (   [CheckPayableTo] LIKE '*&*'
       OR [CheckPayableTo] LIKE '*and/or*'
       OR [CheckPayableTo] LIKE ' *and* ')

 

[misscrf]

The problem is that I need where [CheckPayableTo] NOT LIKE [CustName]... I can't do *, because anything after it could include a change like "and on behalf of blah blah blah"

I need exactly [CheckPayableTo] NOT LIKE [CustName]

and I also need exactly [CheckPayableTo] NOT LIKE [CustName]&" "& [Add1]

If I do an OR, I get all of the first as the or gives me 1 or the other. (I said that bad, but something tells me you get the point)

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Golom]

WHERE [CheckPayableTo] <> [CustName] 
  AND [CheckPayableTo] <> [CustName] & " " & [CustAddr1]
  OR  (   [CheckPayableTo] LIKE '*&*'
       OR [CheckPayableTo] LIKE '*and/or*'
       OR [CheckPayableTo] LIKE ' *and* ')

assuming that [Custname] will never contain '&', 'and/or' or ' and '.

 

[misscrf]

You make a great point at the bottom. We have to keep those, because we will need them even if they are the same, if they have an '&' or 'and'.

the only thing I changed is <> to Not (they are text fields and the <> didnt seem to work.)

misscrf

It is never too late to become what you could have been ~ George Eliot