How to use IN and Sub query?

[Jon]

Greeting,

Anyone can explain for me the Using of IN and Sub query?
I have the following situation
I need to List the customers who bought dogs in the first quarter and also
bought dog food in the fourth quarter using IN and Sub query

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

 

[John Spencer]

Guessing at relationships based on your table and field names.

SELECT Customers.*
FROM Customers
WHERE CustomerID in
(SELECT Sales.CustomerID
FROM SaleAnimal INNER JOIN Sales ON SaleAnimal.SaleID = Sales.SaleID
WHERE DatePart("q",SaleDate) = 1)
AND CustomerID in
(SELECT Sales.CustomerID
FROM (SaleItem INNER JOIN Sales ON SaleItem.SaleID = Sales.SaleID )
INNER JOIN Merchandise On Merchandise.ItemID = SaleItem.ItemID
WHERE DatePart("q",SaleDate) = 4 AND Merchandise.Description = "Dog Food")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Anyone can explain for me the Using of IN and Sub query?

This sounds like a homework question. We do not do your homework problems
here.

 

[Daryl S]

Jon -

Basically, the IN clause restricts a field to a list of values. In the
simplest case, you can have SQL like this:

SELECT AnimalID, AnimalBreed from tblAnimals
WHERE AnimalBreed IN ("Great Dane","Golden Retriever","Collie")

With SQL, you can also use a SQL Query in the IN clause to come up with the
values, such as this:

SELECT AnimalID, AnimalBreed from tblAnimals
WHERE AnimalBreed IN (SELECT AnimalBreed from tblBreeds where AnimalSize =
"Large")

In this case, the SELECT statement in the IN clause would return a list of
breeds that were for large animals, and the main SELECT statement would use
that list to find animals of those breeds. You can also use query names
instead of tables for the sources of data for either SELECT statement.

In your case, to get the customer ids with animal sales in first quarter and
food sales in fourth quarter, approach the problem like this:

Create a query (called qryAnimalSales in my code below) that is something
like this:

SELECT Sales.CustomerID, Sales.SaleDate, Sales.SaleID, SaleAnimal.AnimalID
From Sales INNER JOIN SaleAnimal ON Sales.SaleID = SaleAnimal.SaleID

Create a query (called qryFoodSales in my code below) that is something like
this:

SELECT Sales.CustomerID, Sales.SaleDate, Sales.SaleID, SaleItem.ItemID,
Merchandise.Description
FROM Sales INNER JOIN SaleItem ON Sales.SaleID = SaleItem.SaleID INNER JOIN
Merchandise ON Merchandise.ItemID = SaleItem.ItemID

Now pull the customer IDs that have both for the chosen time frames as
follows:

SELECT CustomerID
FROM qryAnimalSales
WHERE SaleDate between #1/1/2009# and #3/1/2009#
AND CustomerID IN (Select CustomerID from qryFoodSales WHERE SaleDate
between #10/1/2009# and #12/31/2009#)

Note that this will pull the CustomerID from the qryAnimalSales, so you can
also pull any fields from that query in your result.

Hope that helps...