Solved

all time entries not within date range are showing up

Posted on 2008-10-13
2
175 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
Comment Utility
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
Comment Utility
YOU ARE THE MAN / WOMAN / ALIEN BEING! EGADDS! THANK YOU!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 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

11 Experts available now in Live!

Get 1:1 Help Now