Okay, been looking through the SQL stuff that Morgue gave me and I realise that I need an Outer Join, but just how to implement it is the thing that is getting me...
Okay, in the database there are two main tables
tbl_BOGStaff (BOGStaffCode(Primary Key), Name, Surname)
tbl_BOG_Time Check (Date (Primary Key), StaffCode(Primary Key), Date, Time_In)
Now I need to craft a Query that selects all the details from tbl_BOGStaff; compares the staff code with the one in tbl_BOG_Time Check, returns the record with the latest date per staff code, compares that with today's date, and if it isn't, flag the person as absent...
Or if there's any way to "minus" the people that did log in from the main list (tbl_BOGStaff) to show the absent that would be awesome! Alas it needs to be in the space of 1 query (I'm presuming it'll be a nested select of sorts)
I didn't design the database hence the fact that I can't just change the tables to suit my needs.
Many thanks in advance
SELECT tbl_BOGStaff.BOGStaffCode, tbl_BOGStaff.Name, tbl_BOGStaff.Surname
FROM tbl_BOGStaff
WHERE (SELECT Count(*) FROM tbl_BOG_Time_Check WHERE tbl_BOG_Time_Check.StaffCode = tbl_BOGStaff.BOGStaffCode AND tbl_BOG_Time_Check.Date = Date()) = 0;
That's whilst watching How I Met Your Mother, season 4, and drinking a rock shandy. SQL is easy.
BTW welcome SQL master... hope you enjoy your stay here on pcd planet
Fixed. Why doesn't it automatically track the thread and notify me of new posts if I've posted on the thread? Surely it's implied?
![Morgue[FLB]](/thumb.php?src=%7Be_AVATAR%7Dupload%2Fap_59_morgueflb.png&aw=100&ah=100)
![Siversmith[PCD]](/thumb.php?src=%7Be_AVATAR%7Dupload%2Fap_7_1216834505_186_ft2896_siver_ava.jpg&aw=100&ah=100)