query to find difference in record sets

I am trying to do the following:

I have one table with students registered in Fall [A], Winter [C] and Spring 
[B] classes. I am trying to find all students that are registered in Fall and 
Spring, but not in Winter.

Query 1 - Find all students registered in the Fall
Query 2 - Find all students registered in the Spring
Query 3 - Find all students registered in both Fall and Spring
Query 4 - Find all students registered in the Winter
Query 5 - Find all students registered in the Fall and Spring but not in 
Winter 


I have the first 4 queries done. I need help with Query 5 to filter out the 
records found in Query 4 from records found in Query 3

The criteria used is the same field each time
The queries results for Query 3 and 4 is the Student ID only
All queries are running on the same table

I know this must be simple but it is eluding me. Thanks for your help!

sandra

0
Utf
1/15/2008 2:34:04 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
978 Views

Similar Articles

[PageSpeed] 41

Have you tried the Wizard query about finding unmatched records? You want 
records from query3 with no match in query4.


The SQL statement may look like:


------------------------------------
SELECT query3.studentID
FROM query3 LEFT JOIN query4
    ON query3.studentID= query4.studentID
WHERE query4.studentID IS NULL
------------------------------------


Note the WHERE clause is applied to the result of the JOIN, NOT to query4 
itself (which, by itself, has probably no studentID being null). Clearly, if 
the join pumps a null under query4.StudentID, it is because it didn't find 
any match, which is what we are looking for.



Vanderghast, Access MVP




"Sandy" <Sandy@discussions.microsoft.com> wrote in message 
news:951EF013-FABA-4700-B16E-5E6E5C5A3482@microsoft.com...
>I am trying to do the following:
>
> I have one table with students registered in Fall [A], Winter [C] and 
> Spring
> [B] classes. I am trying to find all students that are registered in Fall 
> and
> Spring, but not in Winter.
>
> Query 1 - Find all students registered in the Fall
> Query 2 - Find all students registered in the Spring
> Query 3 - Find all students registered in both Fall and Spring
> Query 4 - Find all students registered in the Winter
> Query 5 - Find all students registered in the Fall and Spring but not in
> Winter
>
>
> I have the first 4 queries done. I need help with Query 5 to filter out 
> the
> records found in Query 4 from records found in Query 3
>
> The criteria used is the same field each time
> The queries results for Query 3 and 4 is the Student ID only
> All queries are running on the same table
>
> I know this must be simple but it is eluding me. Thanks for your help!
>
> sandra
> 


0
Michel
1/15/2008 4:23:01 PM
This worked - thanks so much!

"Michel Walsh" wrote:

> Have you tried the Wizard query about finding unmatched records? You want 
> records from query3 with no match in query4.
> 
> 
> The SQL statement may look like:
> 
> 
> ------------------------------------
> SELECT query3.studentID
> FROM query3 LEFT JOIN query4
>     ON query3.studentID= query4.studentID
> WHERE query4.studentID IS NULL
> ------------------------------------
> 
> 
> Note the WHERE clause is applied to the result of the JOIN, NOT to query4 
> itself (which, by itself, has probably no studentID being null). Clearly, if 
> the join pumps a null under query4.StudentID, it is because it didn't find 
> any match, which is what we are looking for.
> 
> 
> 
> Vanderghast, Access MVP
> 
> 
> 
> 
> "Sandy" <Sandy@discussions.microsoft.com> wrote in message 
> news:951EF013-FABA-4700-B16E-5E6E5C5A3482@microsoft.com...
> >I am trying to do the following:
> >
> > I have one table with students registered in Fall [A], Winter [C] and 
> > Spring
> > [B] classes. I am trying to find all students that are registered in Fall 
> > and
> > Spring, but not in Winter.
> >
> > Query 1 - Find all students registered in the Fall
> > Query 2 - Find all students registered in the Spring
> > Query 3 - Find all students registered in both Fall and Spring
> > Query 4 - Find all students registered in the Winter
> > Query 5 - Find all students registered in the Fall and Spring but not in
> > Winter
> >
> >
> > I have the first 4 queries done. I need help with Query 5 to filter out 
> > the
> > records found in Query 4 from records found in Query 3
> >
> > The criteria used is the same field each time
> > The queries results for Query 3 and 4 is the Student ID only
> > All queries are running on the same table
> >
> > I know this must be simple but it is eluding me. Thanks for your help!
> >
> > sandra
> > 
> 
> 
> 
0
Utf
1/15/2008 4:32:03 PM
Reply:

Similar Artilces:

Combo Box Query Help
I have a combo box with hospital names in it. I am wanting to select a hospital from the combo box and have the the address text box automatically fill in with the hospitals address. How can I go about doing this. Please help. -- Message posted via http://www.accessmonster.com On Sat, 29 May 2010 23:00:34 GMT, "Scott_66701 via AccessMonster.com" <u54193@uwe> wrote: One way is to have an extra hidden column in the combobox. For example the rowsource would be a query like this: select HospitalID, HospitalName, Address & " " & City & &quo...

Different passwords for each tab in workbook
Can I set different tabs in a workbook to have a different password. I'd like to be able to distribute a single workbook to a group of sales reps and I don't want them to see each other's information. Tools > Protection > Protect Sheet works on the individual active sheet. Each sheet can have its own password. It's not too hard to crack this password, or to use formulas to retrieve the values in protected sheets. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Pasko1 wrote: ...

Query for random values multiple records
I have a table of names (lots of names! Too many to write individual queries for...). I want three random non-repeating numbers between 1 and 7 for each of these names. Output example: Name1 3 Name1 2 Name1 5 Name2 1 Name2 5 Name2 4 From reading on the boards I was able to come up with a way to generate output such as this: Name1 3 Name1 2 Name1 5 Name2 3 Name2 2 Name2 5 However, that will not work for what I want the random numbers for. I need a different set of each random non-repeated number set for each name. Thanks! On Mon, 16 Apr 200...

Allocating a different cost for an additional team in the same res
dear All, I have the following problem that i need to solve. I have a certain resource where if i use upto 400% of it the charge would be 70$ per hour. However, if the usage exceeds 400%, each additional 100% (i.e. team) sall be charged at 120$ per hour. Any idea how can i reflect this in MS Project 2007. Thank you Plan for (ie budget for) the worst (ie most expensive) case, and then when you track actual costs it will turn out to be better than expected and you (and your boss) will get a pleasant surprise. -- Trevor Rabey 0407213955 61 8 92727485 PERFECT PROJECT P...

Are there MFC subclasses that give a different "look and feel" to the UI?
I was wondering if there are free implementations of MFC subclasses of windows and controls that give you a different look and feel? I like the Windows 2000 look, but it's quite a bit dated now, and the XP look is hideous for my taste, so I'm looking for something new, more modern looking, sleek. Thanks in advance for any suggestions. Franco See if this has something your looking for. http://www.prof-uis.com/ "Franco" <cuminato@yahoo.com> wrote in message news:1149800640.987272.304540@c74g2000cwc.googlegroups.com... >I was wondering if there are free implementa...

Search Function find exact words
Hi, I'm currently using a search function in my db that i found in these newsgroups. Works perfectly. But I would like the user to be able to choose to search for an exact string or to search for part of the data Currently I use: strWhere = strWhere & " AND " & "qry_Personen.Naam Like '*" & Me.Naam & "*'" So when I enter "Pete", i get every entry that has "Pete" in it, so also "Peters" and "Peterson",.... I have added a checkbox next to the field 'Naam', When this checkbox is true ...

Using a Web Query
Hello Everybody I have been trying to retrieve information from the Web by using a Web query, and then inserting the information into an active worksheet in Excel 2000. In the "Returning External Data to Microsoft Excel" dialog box I want to use the Parameters button, which is not active, Could anybody tell me how to activate the Parameters button before using the "Get the value from the following cell" option button? Thank you in advance. Hi. It is possible to supply parameters to a webquery. You can do it programmatically as described in this KB article: http:/...

Any way to find out when Outlook was launched?
A user opened Outlook on his computer (Ofc 2k3 on XP) at some point on Tu, 7/26. I want to find out what time he actually opened Outlook. (NOT what time the first email was received on that date.) So assuming that he opened Outlook (pop3 mode) at some point during the day and them may not have actually sent or received any messages (although Outlook polled the Exchange 2k3 server for new messages every 15 minutes.), is there any sort of log that will tell me when he actually started Outlook on that day? I haven't fount anything that's obviously helpful in the event logs. I&#...

c1041724 restoring on different server
Hello I am trying to restore the exchange server to another box, ive tried replaying the logs and even eseutil /p /i or just /p but the same error, i am running exch 2000 enterprise, an upgrade from sbs. the store wont mount. I have support for chinese and other languages on both the production server and the restore server. I tried restoring AD on this machine but exchange complains about not been the schema master. The ad on this machine has no accounts, i figure after reparing the database i can map the mailboxes but now luck for a few days. Any recommendations apreciated. thanks I got it...

Using different drivers by USB REV
We have an existing product that has our standard USB VID/PID. We haven't set the USB REV to anything so it defaults to FFFF, but the .INF file for our driver doesn't specify the REV so it works fine: USB\VID_1234&PID_1234&MI_00 We now have to introduce a new version of the product that uses the same VID/PID, so we thought we could use the REV to make sure it uses the new driver. The new product sets its REV to 0008 (no particular reason it has to be this) so the .INF file looks like: USB\VID_1234&PID_1234&REV_0008&MI_00 This seems to work fine - the...

Local OLAP cubes in Excel/MS Query
Hi Can someone please recommend some good book or web page about creating local OLAP cubes using MS Query. I have some data in Access that I want to work with using cubes. Kind regards IgorM http://wang.se/en/CreateOLAPCube.html/ Ed Ferrero Hi Thanks for response. Unfortunately I still don't know if the show-details-after-double-click feature is available in pivottables linked to OLAP cubes. I tried all three connection types. With the two first options (rebuild at once and rebuild when needed) I still cannot make the pivot table to create a sheet on the fly and provide detai...

Access can't find form "frmWarning" in macro or code
The error message is actually a little longer than the subject. The form name is referred to in code as Forms("frmWarning"). The name is NOT misspelled. and it is a valid form name. "Kruppy" <Kruppy@discussions.microsoft.com> wrote in message news:331E7802-56F0-40AE-8FEE-4194486F0CB7@microsoft.com... > The error message is actually a little longer than the subject. The form > name > is referred to in code as Forms("frmWarning"). The name is NOT misspelled. > and it is a valid form name. Is the form open? Only open forms are mem...

Different version error using vista
I am getting the following error: "A prodauct on your computer is on a different version than the database version. You will not be able to use the application until this issue is resolved. Use the GP_LoginErrors.log file in your temp directory to assist in resolving this issue." I tried to look for this log on the local but am unable to find it. I spoke to a rep from Dynamics and the said it is compatible with VISTA. it installed great but now I am getting this error. I had the same thing. You should be able to find the log file, however. Another approach is to go to a wor...

SelfJoin query
Hi: Can you please tell me how can I modify this query to identify these 'pairs': 1234A and 1234B; the below query is working when I have 1234A and 1234AX; the characters before X; or in the first case before A and B are the same. Thank you, Dan **** SELECT A1.DEALNO, B1.DEALNO FROM test AS A1 INNER JOIN test AS B1 ON B1.DEALNO = A1.DEALNO & "B"; Hi Marshall: Thank you! It gives me everything; I need just sameA and sameB.... Here is SQL: *** SELECT A.DEALNO, B.DEALNO FROM test AS A INNER JOIN test AS B ON Left(A.DEALNO, Len(A.DEALNO) - 1) = Left(B.DEALNO, Le...

Meeting attendee now set as organizer problem
Hey guys posted this in the Outlook forum but no response... Has anyone seen this? I have a user that recieved a meeting request, when they try to accept the request, outlook says that they are the meeting organizer and that they do not need to accept the meeting. Weird..... Mike hmmm, any type of delegation going on? James Chong (MVP) MCSE | M+, S+, MCTS, Security+ msexchangetips.blogspot.com On Mar 16, 11:18 am, Mike <M...@discussions.microsoft.com> wrote: > Hey guys posted this in the Outlook forum but no response... > > Has anyone seen this? > > I have ...

Different counts on same query
Good afternoon, I need help on finding out how to run three different counts on one query. I have a table set up to show Date Within this query I have the following fields: Date | Method | AgedDays ... taken from TotalInventoryTable my goal is to say the following in one query. Date Method CountofAge CountofAge>30 CountofAge>60 Date Calls ## ## ## Date Corr ## ## ## etc. etc. All on one query... Is this possible. Thank you everyone. -- Message posted via http://www.accessmonster.c...

find entitie with no activities?
Hello, Is it possible to find an entity (like an opportunity) with no open activities using advanced find? Thanks! Jon Did you ever get a response from anyone? I'm looking for something similar...more of a 'can't find' type of query on a related entity. I saw a similar post for using 'NOT' but it's not exactly the same thing. Please let me know - Thanks. Marli "jpop" wrote: > Hello, > > Is it possible to find an entity (like an opportunity) with no open > activities using advanced find? > > Thanks! > Jon > > ...

What is the best way to set up a spreadsheet to do this....
I have been thinking about a problem for days but still haven't decided the best way to set up the spreadsheet, last year it was a logistical nightmare. I have seventeen trainers who will be able to work one or two sessions out of nine sessions i.e. Mon-Fri pm, Sat and Sun am/pm and can have a maximum of five trainees each. Each trainer will only run one training session per availability i.e. if they are available Mon and Fri they will only run a course on either Monday or Friday. Then I have 99 trainees who will be available to attend one or more of the sessions and I have to allocate a...

Add Record button Gives error
In the past, for customer convenience, I have placed an "Add New Record" button in the form header area using the Wizard. No Problem. Now, when I do this, (using the wizard) and try to use the button I get an error message "Module Not Found". When I click on OK, I'm taken to Visual Basic which contains a host of modules in a group labeled ACWZMAIN, which to me is meaningless. The module names are even less enlightening. I'm working with version 2003. Version 97 gave me no problems. What's going wrong and how can I fix it. Frustration level is very high....

prompted twice for parameters when running query
I have created a select query with date range parameters. When I run the query I am prompted to enter start-date and end-date - but am then prompted again. I enter the dates again and the query functions as expected, but I can't work out why I should be prompted twice. Any ideas? -- Dom Sturges Technical Specialist Citi Presentation Technology 1) Variations on the name - if you've declared the parameters as [Enter Start Date ] and then referred to them as [Enter Start Date] (note the space at the end of the first) then you will be prompted for both and only the referred valu...

autosave as different file type
hello. i have encountered a situation, and searches of items already on this newsgroup have not yielded any results. i have a .xls file that can be edited by several members of a development team, and i've written code where i post a report to the web using certain columns of the most current info in this .xls file. however, i use the .csv version of the file for my code for the web page. is there a way that the .xls master file can also be saved as a .csv file whenever someone makes changes? i'm looking for an alternative to doing this save as... task manually every time ...

How do I set up an argument which asks for multiple criteria
I am trying to use an argument that asks for one of three criteria (2345P, 8319T, or 7026J) in cell C108, and if it finds it, enters the amount of yet another cell, G108 into cell K108, and if not enters 0 into cell K108. It seems there are too many arguments for the "if" argument, and I can't seem to get the "lookup" argument to work either. Is there another argument I should be working with? Thanks -- Diver Try: =IF(OR(C108="8319T",C108="2345P",C108="7026J"),G108,0) "bj" wrote: > try in K108 > =if(or(c108=&qu...

Web content query problem .. pls help..
i am using query to retreive data every minutes, however when the tim it refresh, EXCEL itself hang its UI to proceed until all data has bee proceeded... is there any way to make it to do it in the background ? another thing is that .. am i able to import the picture from the we query also? Thank for help.............. -- kitste ----------------------------------------------------------------------- kitster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1495 View this thread: http://www.excelforum.com/showthread.php?threadid=26587 ...

set value of a group of activex control points
Have a spreadsheet that has some 20+ activex control points (option buttons). Is there a way to group all these controls together & set their initial values the same? Trying to setup a "reset" type of operation that would clear all control points. I can do them individually via properties, but it's too time consuming. Any suggestions? ...

calculate date difference
hi, i have a begin date and an end date, i need 2 drag tasks from a stencil onto the page and place it between these dates. i need to calculate the datetime a specific task is positioned at. my units of measure is visyards. I'm assuming, i have to convert my endX & beginX values to visdate to calculate the date value. any directions as to where i can start or eg. pls thanks wouldn't it be easier to just use the timeline functions in the project schedule templates? al "j-in-uk" <jessnair@gmail.com> wrote in message news:1133997102.957608.18340@g47g2000cwa.goo...