Solved

all time entries not within date range are showing up

Posted on 2008-10-13
2
176 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now