in access 2002, using a "not" statement

[Sherri]

we have a school...we are trying to make a list of people that attended
either semester in 2008, but did NOT attend the spring semester 2009; we can
get the 2008 to add up, but then cannot get the "NOT" 2009...HELP!

 

[Jerry Whittle]

Show us the SQL for a query that is somewhat close to working. Open the
query in design view. Next go to View, SQL View and copy and past it here.

 

[Sherri]

Hi Jerry, Thanks...this is where we get the entire 08 attendees (at this
particular class) to show up...

SELECT [LNAME] & " " & [FNAME] AS Expr1, STUDENT.COMPANY,
STUDENT.ADDRESS, STUDENT.CITY, STUDENT.STATE, STUDENT.ZIP,
STUDENT.PHONE, REGISTER.LOC_CODE, REGISTER.[FIELD DATE]
FROM STUDENT INNER JOIN REGISTER ON STUDENT.SS=REGISTER.SS
WHERE (((REGISTER.LOC_CODE) Like "WPB*08"));

 

[vanderghast]

if your table is like:

StudentID Semester ' fields name

(one record per stuend, for each semester the student was registred)

and semester of interest are like "2008_1"


then


SELECT DISTINCT studentID FROM table
WHERE (semester = "2008_1" OR semester = "2008_2")
AND NOT IN(SELECT studentID FROM table
WHERE semester = "2009_1")

is a possible way to do it.



Vanderghast, Access MVP

 

[Sherri]

Thanks Vanderghast...we tried that (or what we think is that!) It took out
the spring 09 records, but left the individuals.

ex. if John Doe attended in the spring of 2008, and the spring of 2009 we
don't want him on the list at all

 

[vanderghast]

You are right, my mistake (it was an invalid sql statement, after all).
Should have been:


SELECT DISTINCT studentID FROM table
WHERE (semester = "2008_1" OR semester = "2008_2")
AND studentID NOT IN(SELECT studentID FROM table
WHERE semester = "2009_1")

was missing studentID in front of NOT IN.


Consider:

SELECT DISTINCT studentID FROM table
WHERE studentID NOT IN(SELECT studentID FROM table
WHERE semester = "2009_1")



that would remove all studentID values which appear somewhere in the table
in semester 2009. Adding any more restrictive criteria in the WHERE clause
with an AND should not bring any more new studentID, but would rather
eliminate some of them, further more. So the initial (corrected) statement
should work.


Note: make a query to just get the StudentID alone, do not bring the
'decorative' fields ( first name, last name, company, zip code, ... ). You
can always bring those decorations in a top most query, with an inner join
on StudentID, if required, but if you try to play with all this irrelevant
data in the 'logic layer' of your process, you may end up by just transfer
data (memory) all around in temporary place, which probably increase
running time... (unless the query is really a simple one, that is).


Vanderghast, Access MVP

 

[John Spencer]

Assuming that the following returns all students that DID attend during
2009 then you can use this query as a sub-query in the FROM clause

SELECT Register.SS
FROM Register
WHERE Loc_Code Like "WPB*09"

So that gives you a query like the following.

SELECT [LNAME] & " " & [FNAME] AS Expr1, STUDENT.COMPANY,
STUDENT.ADDRESS, STUDENT.CITY, STUDENT.STATE, STUDENT.ZIP,
STUDENT.PHONE, REGISTER.LOC_CODE, REGISTER.[FIELD DATE]
FROM (STUDENT INNER JOIN REGISTER ON STUDENT.SS=REGISTER.SS)
LEFT JOIN
(SELECT Register.SS
FROM Register
WHERE Loc_Code Like "WPB*09") as Attend09
ON STUDENT.SS = Attend09.SS
WHERE (((REGISTER.LOC_CODE) Like "WPB*08"))
AND Attend09.SS is NULL


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

Hi Jerry, Thanks...this is where we get the entire 08 attendees (at this
particular class) to show up...

SELECT [LNAME] & " " & [FNAME] AS Expr1, STUDENT.COMPANY,
STUDENT.ADDRESS, STUDENT.CITY, STUDENT.STATE, STUDENT.ZIP,
STUDENT.PHONE, REGISTER.LOC_CODE, REGISTER.[FIELD DATE]
FROM STUDENT INNER JOIN REGISTER ON STUDENT.SS=REGISTER.SS
WHERE (((REGISTER.LOC_CODE) Like "WPB*08"));

Show us the SQL for a query that is somewhat close to working. Open the
query in design view. Next go to View, SQL View and copy and past it here.

 

[Sherri]

Hi John,

we're getting somewhere, but not there yet...

we ran the sql statement below: 1) the query (btw-that's what we're trying
to get is a report with the info on it) removed the student's names
altogether (we need the ones that did NOT attend in 2009 to remain) then 2)
when we saved the statement (thinking we'd 'refine' the statement and try
again) it completely deleted those people's records for 2008 & 2009 from the
table (we want it filtered from the query, but not completely removed from
the table)

can you help us refine the statement to get what we need?
Thanks!

Assuming that the following returns all students that DID attend during
2009 then you can use this query as a sub-query in the FROM clause

SELECT Register.SS
FROM Register
WHERE Loc_Code Like "WPB*09"

So that gives you a query like the following.

SELECT [LNAME] & " " & [FNAME] AS Expr1, STUDENT.COMPANY,
STUDENT.ADDRESS, STUDENT.CITY, STUDENT.STATE, STUDENT.ZIP,
STUDENT.PHONE, REGISTER.LOC_CODE, REGISTER.[FIELD DATE]
FROM (STUDENT INNER JOIN REGISTER ON STUDENT.SS=REGISTER.SS)
LEFT JOIN
(SELECT Register.SS
FROM Register
WHERE Loc_Code Like "WPB*09") as Attend09
ON STUDENT.SS = Attend09.SS
WHERE (((REGISTER.LOC_CODE) Like "WPB*08"))
AND Attend09.SS is NULL


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

Hi Jerry, Thanks...this is where we get the entire 08 attendees (at this
particular class) to show up...

SELECT [LNAME] & " " & [FNAME] AS Expr1, STUDENT.COMPANY,
STUDENT.ADDRESS, STUDENT.CITY, STUDENT.STATE, STUDENT.ZIP,
STUDENT.PHONE, REGISTER.LOC_CODE, REGISTER.[FIELD DATE]
FROM STUDENT INNER JOIN REGISTER ON STUDENT.SS=REGISTER.SS
WHERE (((REGISTER.LOC_CODE) Like "WPB*08"));

Show us the SQL for a query that is somewhat close to working. Open the
query in design view. Next go to View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

we have a school...we are trying to make a list of people that attended
either semester in 2008, but did NOT attend the spring semester 2009; we can
get the 2008 to add up, but then cannot get the "NOT" 2009...HELP!

 

[John Spencer MVP]

There is no way that any select query would delete records. So something is
definitely wrong. It might not show records but the records would still be in
the table(s).

Does this simple query show you the people that did attend in 2009?

SELECT Register.SS
FROM Register
WHERE Loc_Code Like "WPB*09"

If that does show you the people that attended in 2009 then open a new query
using the query wizard and select the "Find Unmatched Query Wizard".

Select your original query
in the next dialog select the query that gets people in 2009
in the next dialog click on the SS field in both queries (if it is not present
in your original query, you will have to cancel, add it in, and start over.
in the next dialog choose the fields you want to display

That should end up showing you all the people that were in 2008 and are not in
2009. I did assume that SS was a unique value that identified a specific
person. If that is wrong, then what value do you have that does identify a
specific person?


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

 

[Sherri]

Hi John,

That still didn't quite work. Do you have a number where I can have Debbie
call you? She's our registrar and the one working on this problem. I think
she'd like to talk to you while she's got the info in front of her...thanks!

 

[John Spencer MVP]

A search on the information in my signature, should give you sufficient
information to contact me. However, that kind of support is usually paid
support. Since you are a school (what school by the way) I will take a call
from Debbie and will spend a few minutes in conversation with her to try to
help her solve the problem.

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