Employee Time in and Time out entry (1 Viewer)

[gobyg]

Hello,

I am trying to create an Access database where I can let the employees enter their time-in and time-out, at the end of their shift.
I already created this function working, but running into another issue.

Everyday, employees need to enter a break. Sometimes they take a break and sometimes they don't, if they work shorter hours. Thus, the break is not a default 30min and has to be enterd by the employee.

Below is the code I have to calculate the total hour:

txtCalcTime has the below code in control source:
=Int([CalcTime]/60) & ":" & Int([CalcTime] Mod 60)

Control source for this form "FrmTimes" property, in which Txtcalctime resides:
QryTimes

"QryTimes" query has the code below:

SELECT TimeID, TimeIn, TimeOut, Brake, DateID, DateDiff("n",[TimeIn],[TimeOut]) AS CalcTime
FROM EmpTimes;


This shows the total number of hours worked. But how can I make it to substract the break time entered by the employee in "txtbreak" on FrmTimes

 

[plog]

The break should be a seperate record with in and out times, then you would create an aggregate query (one that uses GROUP BY) to SUM the total times of. If a record is a break time, it would evaulate to a negative number and when summed would subtract from total time.

 

[gobyg]

I do have two seperate records for time-in and time-out. But the problem of summing up the total time would be it's date/time data type. SUM would only work for integers. Thus, any kind of calculation wouldn't work as it would in integer environment. Also, when i try to enter a value in "TxtBreak" (date/time field), it changes it to a time. For example if I enter 0.50, it'll change it to 12.30am.

 

[plog]

I do have two seperate records for time-in and time-out

According to you're query you don't. That data is in the same record based on the SQL you provided.

You are also correct that you can only sum numbers. Which is why you would use the DateDiff function (http://www.techonthenet.com/access/functions/date/datediff.php) to get the difference in hours between TimeIn and TimeOut.

 

[pbaldy]

FYI I moved your thread to a more appropriate forum.

 

[gobyg]

Plog,

I kind of stuck here. Can you please elaborate a little more on your thoughts.
I just put this in another txtbox calld txttottime and changed txtbreak to double
=Int(Sum([CalcTime])/60-[Brake]) & ":" & Int(Sum([CalcTime]) Mod 60)

but when I enter 0.50 in txtbreak , txttottime is deducting an hour.

 

[plog]

I have no idea what we are talking about any more. If you need help with a form, post a question in that section. If you want help with your table structure or a query I can help.

You keep trying to mix the two. Focus on your table structure and queries, then the forms.

 

[gobyg]

OK,
I am not sure how to get this SQL modified to accomadate my needs.
can you please give me a code to do the Sum and then substract the break.

 

[plog]

Let's assume this is your table:

EmpTimes
Employee, TimeIn, TimeOut, Break
12, "7/6/2013 8:00 AM", "7/6/2013 11:30 AM", False
12, "7/6/2013 11:30 AM", "7/6/2013 11:52 AM", True
12, "7/6/2013 11:52 AM", "7/6/2013 3:00 PM", False

In plain English: the employee clocked in at 8 am, went on a 22 minute break at 11:30 and then worked until 3 pm. Which means they worked a total of 398 minutes. This would be the query to get that data:

SELECT Employee, SUM(Iif(Break), -1, 1) * DateDiff("n", TimeIn, TimeOut)) AS TotalMinutesWorked 
GROUP BY Employee
FROM EmpTimes;

The real key is the TotalMinutesWorked calculated field:

SUM(Iif(Break), -1, 1) * DateDiff("n", TimeIn, TimeOut))

What it does is gets the time difference in minutes between a record's TimeIn and TimeOut fields. Then, if it was a break, it makes it a negative number. Then finally it adds up all those values.

 

[MarkK]

I recommend a table called Punches, or something like that, with a structure like . . .

tblPunch
PunchID (PK)
EmployeeID (FK)
DateTime
Direction

. . . where the field Direction is either a 1 or a -1, where a punch out is 1 and a punch in is -1.

Then you can calculate the employees time (and dates are stored as numbers so you can Sum() them) like this . . .

SELECT Sum(Direction * DateTime) / 24 As Hours 
FROM tblPunch
WHERE <specify employee and date period to check>

. . . so where direction is negative, that DateTime is subtracted, and where the Direction is positive the DateTime is added. This results in a very simple calculation of time between the punch in and punch out events.

-1 *  9:00     - 9
+1 * 12:00    + 12
-1 * 13:00    - 13
+1 * 17:00    + 17
==========    ====
=                7

Then you can write various user friendly interfaces to make the data entry make sense to users, so maybe you want a separate form for breaks, and a big PunchIn button somewhere, and a big PunchOut button, and so on.

 

[DonkeyKong]

Why don't you just have the employee clock out on break and then clock back in after break? Then the break is the difference between the time out before break and the time in after break... Seems a lot simpler than figuring out time direction and if they don't take a break you can figure that out too.

 

[gobyg]

Paul,
I am getting the error below when I try that Query.

The SELECT statement includes a reserved word or an argument nam e that is misspelled or missing, or the punctuation is incorrect.

I am not really familier with complex Sql codings, thought I removed an extra ")", at the end of the query. But still getting the same error.

 

[gobyg]

Below code works perfectly with my original query modification:

SELECT EmpTimes.TimeIn, EmpTimes.TimeOut, EmpTimes.Break, DateDiff("n",[TimeIn],[TimeOut])-break*60 AS CalcTime, EmpTimes.TimeID, EmpTimes.DateID
FROM EmpTimes;

The only issue is that I need the total hours in like 7.50 or 7.25 or 7.75. Instead I am getterin 7.30, 7.15. 7.45.

Is there anyway I can change this over to the other format.