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.

 

[Pat Hartman]

You don't want to use AND as the relational operator. That would only get you records where someone has come in late and gone home early on the same day. You need to connect the conditions with OR. 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.

 

[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.

 

[Pat Hartman]

I don't think the criteria actually works. Between>#06:00#And#07:00# is probably ok since the relational operator is > and 07:00 sets an upper bound but for Between <#13:00#And#14:00# the relational operator is < and there is no lower bound so any logout time < 13:00 would satisfy the condition.

Assuming there is a shift code on each employee record, you could create a table with the values for each shift.
ShiftNum, ClockedIn, ClockedOut
1, 6:00, 14:00
2, 14:00, 22:00
3, 23:00, 6:00

Select ...
From YourTable Inner Join tblShiftHours On YourTable.ShiftNum = tblShiftHours.ShiftNum
Where YourTable.timclockedin > tblShiftHours.ClockedIn OR YourTable.timeclockout < tblShiftHours.ClockedOut;

This query will give you all late ins or early outs regardless of the shift so you only need a single query. If you want to add a shift parameter, you could have the user specify the shift he wants to see data for.

 

[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 ?

 

[Pat Hartman]

I omitted the Select clause in my example because I didn't know what columns you wanted. You need to either supply the names of specific columns or use the asterisk to select all columns:

Select fld1, fld2, fld3
From .....

 

[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

 

[Pat Hartman]

If ShiftNum is numeric, you should not surround the parameter with quotes.

 

[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 !

 

[Pat Hartman]

Your clockin and clockout fields probably contain the date as well as the time, therefore you need to extract just the time to compare to the standard shift table.

Select ...
From YourTable Inner Join tblShiftHours On YourTable.ShiftNum = tblShiftHours.ShiftNum
Where TimeValue(YourTable.timclockedin) > tblShiftHours.ClockedIn OR TimeValue(YourTable.timeclockout) < tblShiftHours.ClockedOut;

 

[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

 

[Pat Hartman]

The format of your Date/Time fields is irelevent. The format has no effect on what is actually stored in the field. How are you populating the fldTimeClockedIN and fldTimeClockedOut fields?

ShiftNum should be an Integer rather than a Double. It will never contain any decimal value.

tblClockTimes needs a primary key. You can use a compound key of ShiftNum, fldTimeEmpID, and fldTmeClockDate. In design view of the table, hold down the cntl key while you click on each of the three fields. Then when they are all selected, click on the key icon in the toolbar.

 

[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));

 

[Pat Hartman]

I am confused. Your first statement is that the query does not work and your second statement is that it does. Exactly what does your posted query return?

 

[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.

 

[Pat Hartman]

It would be easier to solve the problem if you posted the query that didn't work rather than the one that did. Your query should look like:

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

Pay attention to the parentheses. They are critical when combing AND and OR and NOT conditions in a single statement. The parentheses in boolean logic work exactly the same way they do in math. They control the order of presidence.

 

[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.