Solved

all time entries not within date range are showing up

Posted on 2008-10-13
2
179 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:mikesExpertExchange
2 Comments
 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
ID: 22704797
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
 
LVL 1

Author Closing Comment

by:mikesExpertExchange
ID: 31407076
YOU ARE THE MAN / WOMAN / ALIEN BEING! EGADDS! THANK YOU!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question