Specific Query

[jeffjeff]

Hi Guys,

I am facing a difficulty in creating an access query as I want it to be,

So, I have a set of data like this

Manager - Customer

A _______ Walmart
A _______ Target
A _______ Kmart
B _______ Walmart
B _______ Target
B _______Kmart
C _______Walmart


C _______Target
C _______Kmart


so to summarize, each manager has 3 customers which is the same (Walmart, Target, and Kmart)

The question is,
I would like to make a query to exclude

A - Walmart
B - Target
C- Kmart


I know how to exclude only one of them
by doing in the query design:


-------------- Manager --------------------Customer

Criteria ____<>"A"
or ___________________________<>"Walmart"

it works this way,
however, when I combine all those 3 criterias,
it doesn't give me the query I want

Anybody can help me?

Thanks in advance
any form of help is much appreciated..

cheers
Jeff

[JoeM]

Use this crtieria:

WHERE 
NOT ((Manager="A") AND (Customer="Walmart")) 
AND NOT ((Manager="B") AND (Customer="Target")) 
AND NOT ((Manager="C") AND (Customer="Kmart"));

[jeffjeff]

Hi Joe, Thanks for the reply,

So I have to use that criteria in SQL format?
Thanks

[JoeM]

Just switch your query to SQL View, and replace your WHERE clause with the code that I gave you (assuming those really are your field names).
If you switch back to Design View, you can see what it looks like it Access Query Builder.

[jeffjeff]

Just switch your query to SQL View, and replace your WHERE clause with the code that I gave you (assuming those really are your field names).
If you switch back to Design View, you can see what it looks like it Access Query Builder.

Thanks so much Joe for the Help...

[JoeM]

You're welcome!

I find that sometimes when the criteria gets a little complex, it can be easier to type it directly in SQL View rather than try to figure out how to configure it in Query Builder.

[orange]

jeffjeff,

Sometimes, if you have several combinations you want to exclude, it can be easier to put the exclusions in a separate table.

Then design the query with the 2 tables, select only the records that are not in the exclusion table.
See this link

[jeffjeff]

jeffjeff,

Sometimes, if you have several combinations you want to exclude, it can be easier to put the exclusions in a separate table.

Then design the query with the 2 tables, select only the records that are not in the exclusion table.
See this link

Thanks so much orange,

I took a look and it's a great idea