How to use IN and Sub query?

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


0
Utf
11/25/2009 12:32:05 PM
access 16762 articles. 3 followers. Follow

3 Replies
689 Views

Similar Articles

[PageSpeed] 43

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

Jon wrote:
> 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
> 
> 
0
John
11/25/2009 4:27:57 PM
>> 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.

-- 
Build a little, test a little.


"Jon" wrote:

> 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
> 
> 
0
Utf
11/25/2009 4:43:02 PM
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...



-- 
Daryl S


"Jon" wrote:

> 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
> 
> 
0
Utf
11/25/2009 5:15:02 PM
Reply:

Similar Artilces:

Using VLOOKUP in an array
Group: When I use VLOOKUP in an array as follows, the formula returns only the result of C5 times (D5 through D10): =SUM(VLOOKUP(B36,Returns,C5:C10))*(D5:D10) I'm trying to get C5 times D5, C6 times D6, etc. I've been struggling with this for a week. How can VLOOKUP be used properly in an array? Robert ___________________________ Robert Ades Accountancy Corp. 12100 Wilshire Blvd., Suite 660 Los Angeles, CA 90025 Tel: 310-571-1224 Fax: 310-571-1227 robert@robertades.com Hi something like =SUMPRODUCT(--(Returns=B36),C5:C10,D5:D10) not sure how 'Returns' is defined bu...

lotus uses {?} for input, what does excel use (macros)
i can't figure out how to get the macro to stop for the input. what is the designation in the macro to wait for user input? Here are 2 methods Reply = MsgBox(Prompt:="Tell me what to do", Buttons:=vbYesNoCancel, Title:="Give me info") Namestring = InputBox(Prompt:="You name please.", Title:="Enter Name", Default:="Name Please") Mike "jmoore" wrote: > i can't figure out how to get the macro to stop for the input. what is the > designation in the macro to wait for user input? Check out VBA help on InputBox ex...

Show values from non-subtotaled columns when doing a sub-total?
I am trying to have Excel carry values from non-subtotaled cloumns to show up on the subtotal row. What values are you trying to show? Instead of subtotals, you may find it easier, and more flexible, to use a Pivot Table to summarize the data. There are instructions in Excel's Help, and Jon Peltier has information and links: http://peltiertech.com/Excel/Pivots/pivotstart.htm Jim wrote: > I am trying to have Excel carry values from non-subtotaled cloumns to show up > on the subtotal row. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech...

How can I use SBCYahoo and outlook
How do I use outlook when I have an SBCYahoo DSL email account where I send and get email. How can I use both? "Slava" <Slava@discussions.microsoft.com> wrote in message news:D9B8E370-1A54-47D6-AD87-207BA8C87E82@microsoft.com... > How do I use outlook when I have an SBCYahoo DSL email account where I > send > and get email. How can I use both? Call SBCYahoo to ask them what mail hosts to use and how to setup Outlook Express (since that may be the only client they choose to support since obviously they won't have the time or manpower to support every e-mail ...

Using Vista/Win7 common roaming profiles
Our school environment is currently on XP. We find with roaming profiles and multiple users sharing the login (student class logins), you end up with too many corrupt profiles left on machines all around causing all sorts of problems. To get around this, I have a single user profile that accounts only have read only access too when they log on. When they log off no changes they make can be uploaded so no profile can be corrupted. Every time the machine starts up or shuts down it runs a script while deletes local copies of roaming profiles. This has worked an absolute treat...

How to add/remove printers using group policy
I'm the Accidental IT admin for my church. The previous admin used Group Policy to add the wrong printer to all our PCs. The staff would like it removed permanently (it comes back every time they log on). I'm very unfamiliar with the group policy editor. Can any of you give me any tips on how to do this using gpedit? Our system consists of an SBS 2008 server, a Win 2008 server (running SQL), and a mix of Win XP Pro, Vista Pro and Win 7 Pro clients. Things that (I think) that I specifically need to know include: - "gpedit" is the correct command to run? - W...

using PowerShell to toggle the "Snap To" Mouse property
I have been trying to toggle the "Snap To" Mouse property in XP using these PowerShell commands: PS C:> Set-ItemProperty -path 'HKCU:\Control Panel\Mouse' -name SnapToDefaultButton -value 0 PS C:> Set-ItemProperty -path 'HKCU:\Control Panel\Mouse' -name SnapToDefaultButton -value 1 They do indeed toggle the values of that registry value that the Control Panel/Mouse GUI Pointer Options "Snap To" checkbox toggles, but the app level (even a refreshed Control Panel itself) does not honor the new value. What am I missing? - Larry...

Menu problem after installing HR
We just installed the HR module. Now when I go to an employee card and click the GO TO button, and select payroll, the BENEFIT and DEDUCTION buttons are both dimmed. These were available before we added HR. Is there some setting that I missed? Found the problem. In user setup, the Use Payroll view was not checked. "Bob Harrison" wrote: > We just installed the HR module. Now when I go to an employee card and click > the GO TO button, and select payroll, the BENEFIT and DEDUCTION buttons are > both dimmed. These were available before we added HR. Is there some setting ...

Microsoft Software used in Alternative Fuel Source
To whom this may concern, I have developed a way to used an "alternative fuel source" that will create jobs nation wide and with a bit of development, can reach out globally. In order for me to disclose more information about the subject I must receive interest and feedback from Microsoft. I have sent similar messages to Spain's Modragon as well as other leading corporations here in the U.S. The first to act will receive my full devotion to the company. If this message does not apply to this department please forward to the correct cite please. Sincerely, Anto...

Index, match, multiple IFs query
I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. I’ve done some searching on the net and also looked at Microsoft’s help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. I’ve ...

Insert a custom function in a cell by runnig a sub or through a macro.
I have a function named mysub and with parameters that has to be supplied to the function. I want that when the cell where the function is to be inserted, the user can type the arguments of the function sub after they have run the sub. Lets say, that after the sub is run, they get =mysub(..) with the cursor in between the parenthesis and so that they can move with typing of the arguments/parameter of the function. So, in summary, the advantage is that, the user should not type =mysub through the keyboard or they donot even need to remember that provied that there are large numbe...

How to show sub-toal level?
I used subtotal funtion and it gave the subtotal number but on the lst it did not show the subtotal level as it used to be. I restarted computer and run again but it still dow not work. Is there any thing i can do to fix this problem? Thanks Check your other post. ML wrote: > > I used subtotal funtion and it gave the subtotal number but on the lst it did > not show the subtotal level as it used to be. I restarted computer and run > again but it still dow not work. Is there any thing i can do to fix this > problem? > > Thanks -- Dave Peterson ...

Query calculated field question
hello I have a query with fields ID, Level, a1, a2, a3, a4,......... a40. The fields a1 to a40 will either be blank or will have an integer value from 1 to 5. I'd like to add a calculated field, called Score, that will give me the sum of these 40 columns divided by (5 times the number of fields that have values). How should I write this calculated field. b) The 40 fields mentioned above have textboxes on a form. Is it generally better to just do the above calculation for Score on the form, or get the calculated value of Score from the query? Victoria, can I suggest you t...

formula query #3
I have a group of 52 "weekly" spreadsheets , each one is named after this week's start date in a prcise format e.g. 130305.xls. I want to create a cell within each weekly sheet which simply contains this weeks date. Obviously I could manually enter this data but I would prefer it if Excel could automatically access the filename of this spreadsheet and embed it accordingly in the cell. Can anyone tell me if this is possible? TIA Wayne Chip Pearson has formulas for returning the Sheet Name or the File Name: http://www.cpearson.com/excel/excelF.htm#FileName Wayne K...

using money w/ bank of america
i am brand new to using money and i have the 2003 deluxe version. i can go online and sign into bank of america and see all of my accounts that way, but i know that money has a way of synching with bofa. i have two credit cards and two checking accounts and a savings account that i would like to have real time balances and transaction on. could someone please reply with the steps to setting this up. thank you ...

Updating a query criteria from a listbox
Hi, I've written a query that is dependent on a date selected from a listbox the result from the listbox is linked to the querie as follows: [forms]![Main Menu]![Date_Select] This works great when I clickthe button that loads the subform that uses the results from this querie... However when I select a different date in the listbox and click again the new date is not being passed down to the querie. I've tried everything I know and it just doesn't want to work... The listbox is linked to a table containing all the dates to be displayed - if that makes any difference. Any h...

wipe out AR sub ledger
I have to wipe-out the subledger of AR. I would want to wipe out the subledger start from scratch and re-add subledger as of August 31st. Can this be done easily? This can be done manually using sql tools or you could simply go to File>>Maintenance>>Clear Data. neil njp@logicalsolutionsllc.com "2020" wrote: > I have to wipe-out the subledger of AR. I would want to wipe out the > subledger start from scratch and re-add subledger as of August 31st. Can this > be done easily? > > but what data would I clear? I am not the db person in the company ...

How do I get my clip art to print in color using Publisher?
How do I get my clip art to print in color using Publisher? I've tried what seems like everything to print the art work in color. And yes, I do have color capability on my printer. On Thu, 11 May 2006 22:43:01 +0100, universe wrote (in article <D0F48D12-24AD-4855-BDFA-066A306768F4@microsoft.com>): > How do I get my clip art to print in color using Publisher? I've tried what > seems like everything to print the art work in color. And yes, I do have > color capability on my printer. This is nothing whatsoever to do with Publisher. This is a printer issue. Mayb...

Opening Publisher 98 file using publisher 2000
I have a very important document authored using Pub98. Unfortunately, I have upgraded to Pub2000 and when i try to open the pub98 file i get the error message "cannot open file from a different version " Surely there must be a way to open the file with Pub 2000. A very important contract hangs in the balance - Any help would be greatly appreceiated. Thanks Grant By rights you should be able to open the file. If you have Norton: How to use Office programs with the Norton AntiVirus Office plug-in http://support.microsoft.com/kb/329820/en-us If the file still will not open, sen...

not sure if I'm using the correct formula for the result I want?
I'm trying to calculate a 'low net' score. A persons 'actual score' minus 'their handicap' = 'low net' score. I have this worksheet setup like this; A4:A23 = list of names Columns B-U have the weekly calculated low net scores B4:U23 = the calculated 'low net' scores using this formula =SUM(Scorecard!B4-Scorecard!Y4); (this is for player one) Scorecard!B4 is player one's first week 'actual score', Scorecard!Y4 is player one's calculated 'handicap'. In Row B25:U25 I have this formula {=MIN(IF(B$4:B$23>0,B$4:B$23))}, t...

Help Please
How can I update on an address list "Po address" name to read P.O. with out changing the rest of the address. I just want to change the "Po to read "P.O."? Any help will be greatly appreciated. Thank you. UPDATE YourTable SET Address = "P.O. " & MID([Address],4) WHERE Address Like "PO *" Assumption: Addresses to be changed all start with PO and a space If PO can be buried within the address (Route to PO Box 2132 Westchester) then it becomes a bit more complex UPDATE YourTable SET Address =TRIM( Replace(" " & [Address],&q...

Help, I moved mailboxes and now can open boxes using "Service account admin" rights
Hey everyone, We are running a Windows 2003 domain and Exchange 2003 sp1 on Win2k3 in a cluster. We had ONE storage group and 4 DBs. I have a AD group that has Exchange "Service account admin" rights and people in that group could open ANY mailbox. I am in that group too. Over the weekend we created a NEW storage group in Exchange and MOVED tow DBs from SG1 to SG2 (the new SG). Now I can't open anyone's mailbox that is in the new SG. The users are working fine. They can connect to their mailboxes using Outlook 2003 just fine. However, I am troubleshooting a Bl...

Offline Folders: Added Sub-folders are not made off-line
I am using XP and use offline folders to keep important data on / up to date on my laptop. I have noticed that if I have a parent folder set to available offline, if I add a sub-folder then this sub-folder and its contents are available offline. However if someone else adds a sub-folder then this sub-folder (and its contents) are not automatically available offline (even though the parent folder is and theoretically all subfolders beneath it). Has anyone else noticed this problem? Is there a fix? As it is I have to periodically look through the folders to see if any new ...

Project Return should allow for use of originating currency
Transactions | Project | Returns from Project Entry should allow for use of an originating currency in multicurrency environments, rather than assuming the functional currency for all transactions. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.c...

Import CSV files using Macro
i have 41 csv files that i need to import into 41 sheets in Excel. the name of the csv files and the sheet names are the same minus the extension (.csv). The sheets will be existing sheets with headers. Please Help the Novice thanks M3ntz Well, one way would be to use the Data->Import External Data->New Database Query and select CSV files as the source database. Drawback: it's completey manual & you'll have to go through it 41 times Another way is to choose File->Open, select all 41 CSV files, and let Excel open them. Each will open in its own workbook. You ...