A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

'Not in' Query



 
 
Thread Tools Display Modes
  #1  
Old May 24th, 2004, 04:37 PM
John
external usenet poster
 
Posts: n/a
Default 'Not in' Query

Hi, I have a few tables and want to run a query, but am
not sure how the sql will work out. I have a table that
has a list of students, and a results table that lists all
students and the corresponding classes they have taken
(also have a class table). What I want to do is run a
query that will show me all people who have NOT taken a
class. My results table has a list of people and classes
they have taken, and can't figure out how to run a query
that will ask the user for a class and then reference
everybody who hasn't taken that class....Any help would be
greatly appreciated.

Thanks a lot!!!
  #2  
Old May 24th, 2004, 05:11 PM
Basil
external usenet poster
 
Posts: n/a
Default 'Not in' Query

A quick thought that I once used for something similar...

Group on student, have a calculated field that says Expr1: Min(IIf(Class=[My Class],0,1)) and have a where clause on the field only where Expr1 = 1

I would say [My Class] would be a parameter from your form.

Basil

----- John wrote: -----

Hi, I have a few tables and want to run a query, but am
not sure how the sql will work out. I have a table that
has a list of students, and a results table that lists all
students and the corresponding classes they have taken
(also have a class table). What I want to do is run a
query that will show me all people who have NOT taken a
class. My results table has a list of people and classes
they have taken, and can't figure out how to run a query
that will ask the user for a class and then reference
everybody who hasn't taken that class....Any help would be
greatly appreciated.

Thanks a lot!!!

  #3  
Old May 24th, 2004, 05:16 PM
Newbie
external usenet poster
 
Posts: n/a
Default 'Not in' Query

Try something like: (not tested)

SELECT StudentList.StudentID
FROM StudentList LEFT JOIN StudentClasses ON StudentList.StudentID =
StudentClasses.StudentID
WHERE StudentClasses.StudentID Is Null

HTH

"John" wrote in message
...
Hi, I have a few tables and want to run a query, but am
not sure how the sql will work out. I have a table that
has a list of students, and a results table that lists all
students and the corresponding classes they have taken
(also have a class table). What I want to do is run a
query that will show me all people who have NOT taken a
class. My results table has a list of people and classes
they have taken, and can't figure out how to run a query
that will ask the user for a class and then reference
everybody who hasn't taken that class....Any help would be
greatly appreciated.

Thanks a lot!!!



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.