Weeding out poor timekeepers !

[jon92]

I have a table which includes two fields - [timeclockedin] and [timeclockedout] What I would like to be able to do is - run a weekly check on these two fields and check if staff have clocked in late or clocked out early. Example an early shift works 06:00 - 14:00, so I was thinking along the lines of > 06:00 [timeclockedin] and < 14:00 [timeclockedout].

Would this be possible in a query or do I need to use code ?

 

[dcx693]

You don't need any code - you've got the query criteria right there. You'll just need some way of telling the query when someone was expected to clock in and out.

 

[jon92]

Thanks Pat

Or get a little fancier and actually calculate the number of hours worked. That way if someone came in an hour late but worked an hour late you would not want to select that record.

I can't use the fancy method - The shifts are set 8hrs ie the 06:00-14:00 shift is relieved by the 14:00 - 22:00 shift.

I have opted for
Between>#06:00#And#07:00#[timclockedin]
OR Between <#13:00#And#14:00#[timeclockedout]
This seems to give the results I require.

Now all I have to decide is whether to have 3 queries for the 3 shifts, or code to change the criteria from within the form I use to view and edit records.

 

[jon92]

Pat

I have tried what you suggest using the following:

SELECT FROM tbldefaultshifts INNER JOIN tblclocktimes ON tbldefaultshifts.ShiftNum = tblclocktimes.ShiftNum
WHERE (((tblclocktimes.fldtimeclockin)>[tbldefaultshifts].[flddefaultin])) OR (((tblclocktimes.fldtimeclockout)<[tbldefaultshifts].[flddefaultout]));

this results in error message: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Where have I gone wrong ?

 

[jon92]

Pat

Thanks once again, I seem to be getting the results I require now
I have tried to add a shift parameter (Iv'e named them 1 2 + 3) if I put "1" as the criteria for tblmytable.ShiftNum it gives records for 1 2 + 3

 

[jon92]

Pat

Yes [ShiftNum] is numeric and the parameter is not surrounded by quotes.
I think I will admit defeat and go back to the drawing board with my table(s) design I dont' think they are quite right - Thanks for your patience !

 

[jon92]

Pat

Sorry it's still not working. My tables structures are as follows:

tblEmployees
fldEmpID - text, PK
fldEmpLastName - text
fldEmpFirstName - text
fldEmpPayroll - text

tblDefaultShifts
ShiftNum - number,Double, PK
fldDefaultIn - date/time, format - short time
fldDefaultOut - date/time, format - short time

tblClockTimes
ShiftNum - Number, Double
fldTimeEmpID - text, indexed duplicates OK
fldTmeClockDate - date/time, format - dd/mm/yy
fldTimeClockedIN - date/time, format - short time
fldTimeClockedOut - date/time, format - short time

tblEmployees 1-To- Many with tblClockTimes

tblDefaultShifts 1-To-Many with tblClockTimes

 

[jon92]

Sorry for the delay replying.
I have made the changes you suggested but I'm still getting the same results from the query. Re - populating the two time fields - I am doing this manually (I get a weekly printout from Human Resources for my derpartment)

I have tried running the query with just the fldtimeclockedIN selected and this gives me the correct results and also allows we to specify a parameter for the ShiftNum field.

SELECT tblclocktimes.fldtimeclockin, tblclocktimes.ShiftNum
FROM tblclocktimes INNER JOIN tbldefaultshifts ON tblclocktimes.ShiftNum = tbldefaultshifts.ShiftNum
WHERE (((tblclocktimes.fldtimeclockin)>[tbldefaultshifts].[flddefaultin]) AND ((tblclocktimes.ShiftNum)=1));

 

[jon92]

Pat
Sorry I'm confusing you. The query posted returns times in tblclocktimes.fldtimeclockin that are greater than 06:00 (I have 1 as the criteria for tblclocktimes.ShiftNum) Therfore if staff clockin on time or just before 06:00 the query won't return anything fine so far. But if someone clocks in on time all week but leaves early one day (before 14:00) the posted query wouldn't pick that up because the tblclocktimes.fldtimeclockin is not in the statement, however if I add that field to the statement the query seems to ignore the criteria for tblclocktimes.ShiftNum and returns records for shifts 1, 2 & 3 regardless of what number is entered as the parameter.

 

[jon92]

Thank-you for your help & advice I now see where I was going wrong.
I have decided to view the results in a form using the following metbod:


SQL 1
SELECT tblclocktimes.ShiftNum, tblclocktimes.fldtimeEmpID, tblemployee.fldEmpLname, tblclocktimes.fldtimeclockdate, tblclocktimes.fldtimeclockin
FROM tblemployee INNER JOIN (tbldefaultshifts INNER JOIN tblclocktimes ON tbldefaultshifts.ShiftNum = tblclocktimes.ShiftNum) ON tblemployee.fldEmpID = tblclocktimes.fldtimeEmpID
WHERE (((tblclocktimes.ShiftNum)=[Specify Shift Number]) AND ((tblclocktimes.fldtimeclockin)>[tbldefaultshifts].[flddefaultin]));

SQL 2
SELECT tblclocktimes.ShiftNum, tblclocktimes.fldtimeEmpID, tblemployee.fldEmpLname, tblclocktimes.fldtimeclockdate, tblclocktimes.fldtimeclockout
FROM tblemployee INNER JOIN (tbldefaultshifts INNER JOIN tblclocktimes ON tbldefaultshifts.ShiftNum = tblclocktimes.ShiftNum) ON tblemployee.fldEmpID = tblclocktimes.fldtimeEmpID
WHERE (((tblclocktimes.ShiftNum)=[Specify Shift Number]) AND ((tblclocktimes.fldtimeclockout)<[tbldefaultshifts].[flddefaultout]));


Two Buttons on the form with the following code attached to the On Click event

Private Sub cmdViewClockin_Click()
Dim strNewRecord As String
strNewRecord = "SELECT (mysql 1)"
Me.RecordSource = strNewRecord
Me![fldtimeclockin].Visible = True
Me![fldtimeclockout].Visible = False
End Sub


Private Sub cmdViewClockout_Click()
Dim strNewRecord As String
strNewRecord = "SELECT (mysql 2)"
Me.RecordSource = strNewRecord
Me![fldtimeclockin].Visible = False
Me![fldtimeclockout].Visible = True
End Sub

This appears to be giving the results I am looking for.