Excluding a field in query in a one-to-many relationship

[eclypse66]

I have two tables where a particular field can have one to many
attributes. I am wanting to exclude a field in table A if it contains a
particular attribute in Table B. For example, a customer can have 3
types of credit cards ie (Amex, Visa, MC, Discover). If a customer
happens to list Discover as one of their credit cards even though they
may also have Amex and Visa, I want the customer excluded from the
list. I am not sure how to do this. Can anyone help me with this?

 

[Guest]

You can't dynamically exclude a field based on the data. With an IIf
statement you could leave a field empty or null.

However further down you talk about excluding a Customer. Do you want to
exclude just certain data in fields OR exclude an entire record?

Also are all your credit cards listed in one field or column in the table or
do you have Amex, Visa, MC, Discover, etc., fields? Hopefully everything is
in one field.

 

[John Spencer]

EXAMPLEs:

SELECT *
FROM Customers
WHERE CustomerID NOT IN
(SELECT Cards.CustomerID
FROM Cards
WHERE Cards.CardType ="Discover")

OR
SELECT *
FROM Customers INNER JOIN
(SELECT Cards.CustomerID
FROM Cards
WHERE Cards.CardType ="Discover") as DiscoverCustomers
ON Customers.CustomerID = DiscoverCustomers.CustomerID
WHERE DiscoverCustomers.CustomerID is Null

Or two queries.
Build a query that gets all customers that DO have a discover card. Save
that

Use the unmatched query wizard to join the customers table to the saved
query.