Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

all time entries not within date range are showing up

Posted on 2008-10-13
2
Medium Priority
?
185 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:Michael Sterling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 10

Accepted Solution

by:
dwe761 earned 2000 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:Michael Sterling
ID: 31407076
YOU ARE THE MAN / WOMAN / ALIEN BEING! EGADDS! THANK YOU!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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