all time entries not within date range are showing up

this is a contiuation of a previous question (a couple actually) but i wil start fresh. i have an access databae with four tables in it. on table holds student information the other holds time entry information another sport/ team information and a fourth holds information about if a student has had a sign in or out time. (the database is included). i'm running a query (also in the database) that is supposed to return all the students who had or did not have a timeIN / timeOut record for a given period. the query runs and works accept for if a student didn't have a time in / out record for the given period, but did have one or more,  (time in /out record(s)) prior to the start of that given period, the query pulls all of the records that were prior to the start of the given date range instead of just one, or just showing that the student had no time for the given date range. What i want to show is all of the students (whether they did time or not) for a given date range (historically). I know the part of the query (or a part of the query) that is causing the (or part of ) the problem:

(in the where clause)
OR (ai.StudentID NOT IN (SELECT DISTINCT io1.StudentID
                                 FROM In_Out io1
                                 WHERE (io1.TimeIn>= #09/28/2008#
                                 AND io1.TimeIn<= #10/03/2008 23:59:59#))))

but don't know how to fix it. Any help would be appreciated. Just fyi...The "NoTime" table is used as a lookup to say: "...if there is an entry in this table then the student didn't have time...". this table starts out at the begining of every week with "No Time In" and "No Time Out" for all of the records in the (TimeIn and TimeOut fields) in the table. Then as students sign in they're record (based on the student ID) is updated so that the the TimeIn and TimeOut fields read "HAS TIME".

if i haven't stated my question simply, it is: How do i get the student who's showing up twice (appropriately named "ShowsUp Twice" in the database), to not show up twice when i run the query. I only want one entry to show up if the student didn't have time for the given period

if upon attempting to open the database you're prompted for a password use: "test"; "Admin" is username
NewTest.mdb
LVL 1
Michael SterlingWeb Applications DeveloperAsked:
Who is Participating?
 
dwe761Software EngineerCommented:
You have hidden Group By's that if you showed what they were, you would probably see.  Try this:
 
SELECT ai.LastName+', '+ai.FirstName AS StudentName, ai.StudentID, ai.Required_Hours, s.Sport_Name, IIf(nt.TimeIn='No Time In',nt.TimeIn,io.TimeIn) AS TimeIn, IIf(nt.TimeOut='No Time Out',nt.TimeOut,io.TimeOut) AS TimeOut
FROM ((AthleteInfo AS ai LEFT JOIN In_Out AS io ON ai.StudentID = io.StudentID) INNER JOIN NoTime AS nt ON ai.StudentID = nt.StudentID) LEFT JOIN Sport AS s ON ai.SportID = s.Sport_ID
WHERE (((io.TimeIn)>=#9/28/2008#) AND ((io.TimeOut)<=#10/3/2008 23:59:59#) AND ((ai.SportID)='MBA')) OR (((ai.SportID)='MBA') AND ((ai.StudentID) Not In (SELECT DISTINCT io1.StudentID
                                 FROM In_Out io1
                                 WHERE (io1.TimeIn>= #09/28/2008#
                                 AND io1.TimeIn<= #10/03/2008 23:59:59#))))
GROUP BY ai.LastName+', '+ai.FirstName, ai.StudentID, ai.Required_Hours, s.Sport_Name, IIf(nt.TimeIn='No Time In',nt.TimeIn,io.TimeIn), IIf(nt.TimeOut='No Time Out',nt.TimeOut,io.TimeOut)
ORDER BY ai.LastName+', '+ai.FirstName, s.Sport_Name;
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
YOU ARE THE MAN / WOMAN / ALIEN BEING! EGADDS! THANK YOU!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.