how to add condition to In Clause?

[Jon]

Greeting,

I have a question which is how to list the customers who bought dogs in the
first quarter and also bought dog food in the fourth quarter. My query is as
follows:
SELECT Customer.*
FROM Customer
WHERE (((Customer.CustomerID) In (SELECT Sale.CustomerID
FROM SaleAnimal INNER JOIN Sale ON SaleAnimal.SaleID = Sale.SaleID
WHERE DatePart("q",SaleDate) = 1) And (Customer.CustomerID) In (SELECT
Sale.CustomerID
FROM (SaleItem INNER JOIN Sale ON SaleItem.SaleID = Sale.SaleID )
INNER JOIN Merchandise On Merchandise.ItemID = SaleItem.ItemID
WHERE DatePart("q",SaleDate) = 4 AND Merchandise.Description like "Dog
Food" & "*")));


What is missing in the first In is the animal category which is “Dog†how to
add this criteria to : In (SELECT Sale.CustomerID
FROM SaleAnimal INNER JOIN Sale ON SaleAnimal.SaleID = Sale.SaleID
WHERE DatePart("q",SaleDate) = 1)

My tables are as follows:
Table Animal has
AnimalID
Category

Table Customers
CustomerID
Phone
Name

Table Sales
CustomeriD
SaleDate
SaleID

Table Saleanimal
SaleID
AnimalID
SalePrice

Table SaleItem
SaleiD
ItemID

Table Merchandise
Item ID
Description

 

[Daryl S]

Jon -

See below. You need to join the Animal Table to the query, and then include
the criteria in the WHERE clause:

SELECT Customer.*
FROM Customer
WHERE (((Customer.CustomerID) In (SELECT Sale.CustomerID
FROM (SaleAnimal INNER JOIN Sale ON SaleAnimal.SaleID = Sale.SaleID)
INNER JOIN Animal ON SaleAnimal.AnimalID = Animal.AnimalID)
WHERE DatePart("q",SaleDate) = 1) And Animal.Category = "Dog"
AND (Customer.CustomerID) In (SELECT
Sale.CustomerID
FROM (SaleItem INNER JOIN Sale ON SaleItem.SaleID = Sale.SaleID )
INNER JOIN Merchandise On Merchandise.ItemID = SaleItem.ItemID
WHERE DatePart("q",SaleDate) = 4 AND Merchandise.Description like "Dog
Food" & "*")));