using "AND" in a query

[looperboy]

If I have a customer table, linked to a table with a "Pets" field in it
and I want a list of all customers who have both a CAT AND a DOG a
pets, can I create this query from the Design Query Pane?

It seems very easy to create a list of customers who have either a CA
OR a DOG, which would obviously include those who had both, but how d
I narrow it to those who only have both

 

[Tom Ellison]

Dear Looper:

So, the Pets table is on the many side of a one-to-many relationship
with the Customer table.

I'll use PetType as the name of a field in Pets that says "Dog", "Cat"
or "Tarantula". I'll assume the Customer and Pets table are joined on
CustomerID.

SELECT CustomerName
FROM Customer C
WHERE EXISTS (SELECT * FROM Pets P
WHERE P.CustomerID = C.CustomerID
AND PetType = "Dog")
AND EXISTS (SELECT * FROM Pets P
WHERE P.CustomerID = C.CustomerID
AND PetType = "Cat")

You would have to adapt this to the names of the columns and tables
you have. And it may not be very fast when run in a Jet database,
especially without the optimal indexes. But perhaps it will be a
starting point for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts