Solved

Showing time that Isn't there.

Posted on 2008-09-30
15
191 Views
Last Modified: 2011-10-19
Ok i'll try to explain this and not leave out too many details. If I do please ask and I'll provide them. I'm sure there is a easy solution to this i just cant figure it out right now...here goes: In table A i have an ID (key) that matches an ID in table B. For any given week students check in and out of table B, this creates a record in table B that looks something like:
 
ID | StudentName | TimeIn | TimeOut

Ifi want to query table B for TimeIn and TimeOuts for all students that match a student in table A then,..no big deal:
SELECT
            ID, TimeIn, TimeOut
FROM
           Table A AS a
              INNER JOIN
           Table B AS b ON a.ID = b.ID
WHERE
            TimeIn >= A_dateTime
Now, here is the problem, how do I  show the students that did not have time. In other words I want to show all the students and for those that did not have time display a message like:
"NO TIME" or something along those lines. And yes this is pretty much a "punch clock" or a payroll type of thing. The thing is that in some cases I do get the "NO TIME" students to show up, but not all the time so I'm not getting all of the students to show up...
0
Comment
Question by:mikesExpertExchange
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22611348
You need an outer join:


SELECT
            a.ID, b.TimeIn, b.TimeOut
FROM
           Table A AS a
              LEFT JOIN
           Table B AS b ON a.ID = b.ID
WHERE
            b.TimeIn >= A_dateTime
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 22611440
You probably need an Is Null criteria also to pick up the students who don't have any records in table b

Something like this perhaps,

SELECT TableA.Name, IIf([Time In]>=[Whatever time],[Time In],"No Time") AS [Time in Indicator]
FROM TableA LEFT JOIN Tableb ON TableA.id = TableB.id
WHERE (((TableB.[Time in])>=[Whatever time])) OR (((TableB.[Time in]) Is Null));


Leigh
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 22611904
matthewspatrick: i tried the outer join and still can't collect them all....

dunscombe: i tried your suggestion as well and still don't have them all....is there some other information i can give / provide?
0
 
LVL 17

Expert Comment

by:Natchiket
ID: 22612206
May I suggest another option

SELECT
            a.ID, b.TimeIn, b.TimeOut
FROM
           Table A AS a
              LEFT JOIN
           (SELECT * FROM Table B WHERE TimeIn >= A_dateTime) b
ON a.ID = b.ID

There is something that irks me slightly, though.. Presumably table A contains the student details.  Why does table B have the student name in ?  Surely this should be a property of table A


           
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 22613776
Natchiket: Yes, that is correct, student name is in table A. It does get written to table B when a record is inserted though. Are you saying that it shouldn't be written into table B when a record is created?
0
 
LVL 17

Expert Comment

by:Natchiket
ID: 22622070
well a seasoned database developer will tell you that since the name is already in A, having it in B also is redundant since the ID field in B indicates the record in A and hence the student.  Of course there *may* be some perfectly legitimate reason for having the name also in B, although it's not obvious from the given scenario.  Generally speaking duplicate information in databases is a recipe for trouble because, apart from the overhead of extra strorage space etc, there's the problem of keeping the data consistent i.e. 2 or more tables having to be updated if one of the students changes their name (or needs their name corrected).
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 22628906
There must be something else going on here that I am not getting or misunderstanding.  I tested my query as posted and it worked without problem so I am at a loss as to why it isn't working for you.  The fact that you have the student name in both tables shouldn't make a difference to the results of the query however as Natchiket suggests it is probably not an optimum design.

Is it possible to post a sample of your db that we can take a look at and maybe figure what is going wrong.

Leigh
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 22631069
ldunscombe: yes, i'm attaching a sample of the database, hopefully this will help. i had to change the names of the innocent to protect them, but i also wanted to give you a week's worth of data so instead of thinking up a couple hundred original names i changed everyone to 'Test Name', if this gets confusing let me know and i'll work on a better sample...
Sample.mdb
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 22631071
if it asks you for a password,..try test
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 22631073
that's 'test'
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 22631476
This seems to work in your sample.

SELECT AthleteInfo.StudentID, AthleteInfo.FirstName, AthleteInfo.LastName, IIf([TimeIn]>=[Pick A Date],[TimeIn],"No Time") AS Tm INTO Temp
FROM AthleteInfo LEFT JOIN In_Out ON AthleteInfo.StudentID = In_Out.StudentID
WHERE (((In_Out.TimeIn)>[Pick a Date])) OR (((In_Out.TimeIn) Is Null));


Leigh
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 22631492
Another thing that you might want to fix.

In your Athlete Info table  your StudentID field should be indexed (NO DUPLICATES)

Leigh
0
 
LVL 14

Accepted Solution

by:
ldunscombe earned 500 total points
ID: 22631511
Sorry, Sorry

Scratch that query,

Thats the one I used to test that all of the Students were included.
This is the one you want.


SELECT AthleteInfo.StudentID, AthleteInfo.FirstName, AthleteInfo.LastName, IIf([TimeIn]>=[Pick A Date],[TimeIn],"No Time") AS Tm
FROM AthleteInfo LEFT JOIN In_Out ON AthleteInfo.StudentID = In_Out.StudentID
WHERE (((In_Out.TimeIn)>[Pick a Date] Or (In_Out.TimeIn) Is Null));

Leigh
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 22670921
ok that seemed to work for the "current" week. i'm having trouble getting all of the students to show, this time, for a previous week in the past (historical). I've attached a sample of the database along with a query (within the database queries) to demonstrate my problem. In this case "Missing Thisone" and "Missing Thisonetoo" do not show up as "NO TIME" and they should as they have no time entered for the period specified. I don't know why though....
HistoricSample.mdb
0
 
LVL 1

Author Closing Comment

by:mikesExpertExchange
ID: 31501835
for the problem of the current week this solution was great
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

15 Experts available now in Live!

Get 1:1 Help Now