Timeclock database help with calculating clock in and clock out

[mmart33]

Hello all,



I have a timeclock software program that runs smoothly that we purchased awhile back. It creates a default report in its software program.

It has a database on our server that it saves all the information of employees and time clocked in and clocked out. I want to create a custom report that anyone can open the database up from the server and run (currently only one computer can have the software as the timeclock and run reports from that one).

I have the database and have started on a query. The issue is, I am not sure how to calculate the times. The way it is setup is there is one TIME field. Every time there is an action for the employees, a new record is created with the current date and time. Then there is an event field that changes from yes (clocked in) to no (clocked out) based on what the previous event was. Employees generally take a lunch each day that is anywhere from 15-30 minutes and will have to take that out of their hours total per day.

Basically i want the report to be able to have each employee as a row and the day(s) selected as the column header with the number of hours for each employee listed. I would like the date to be based off a single day or a date range based on a date selected form.

I have attached the database. The relationships and table were already created. I created / started the only query and form. Can anyone help or modify what I have?

Thanks again for all you guys that help. Really does help with the headaches access causes for me. MODtimeclock.zip

[burrina]

Does this total get taken out now or is this a feature you are wanting? So, you just want to see a single day(s) worth of records per employee? Does this db not let more than one pc use it at a time? You cant just a front end for the db for each user?

[June7]

Field blnEventType is the In/Out indicator. So there should be an even number of records for each day, at least 2, for each employee. Your data doesn't reflect that. There are 7413 records in tblTimes, 3711 are Yes and 3702 are No. For example, employee 4 has 3 records on 02/25/2013.

Presuming In/Out must be in pairs and you fix the dataset, consider:

Query1
SELECT tblTimes.*, Format([datEvent],"mm/dd/yyyy") AS EventDate, tblTimes.lngEmployeeID, tblTimes.datEvent
FROM tblTimes
ORDER BY tblTimes.lngEmployeeID, tblTimes.datEvent;

Query2
SELECT Query1.lngEmployeeID,
Query1.EventDate,
Query1.datEvent,
(SELECT TOP 1 Dupe.datEvent
FROM Query1 AS Dupe
WHERE Dupe.lngEmployeeID = Query1.lngEmployeeID
AND Dupe.EventDate = Query1.EventDate
AND Dupe.datEvent < Query1.datEvent
ORDER BY Dupe.lngEmployeeID, Dupe.datEvent DESC) AS PriorValue
FROM Query1
WHERE blnEventType = False
ORDER BY lngEmployeeID, datEvent;

[qa12dx]

i had done this long time back. the main headache will be if u have shifts that start and one day and end on another. typically night shifts.

how i had done it was i first took all the time record data inserted in a table
empl_cd, date, time1,time2,time3,time4,time5,time6,time7,time8,ti me9,time10 (empl_cd and date is pk, so no dups)

basicaly 5 pairs of time. people take short leave slips to run personal errands or lunch, etc. in my case i found 5 pairs of times sufficed.

1st i had a append query to add empl_cd and date.
then update query to update time1 with min([time]) of the time record table. then i appended that record ID to a logged table containing just these record ID's.
now i did the same for time2,3,4, ... EXCLUDING the ID's in the logged table. so everytime you updated the time, u again run the append query to record the ID

once you have all the empl data by date in a single row, it becomes more simple.

getting the time part of the datetime - TimeValue([datEvent])

my suggestion is if you dont have to do these, don't. the reason is people forget to log in, log out. sometimes people log in more than once, thinking they haven't log-in or out. and since it is tied with salaries, when things go wrong, u'll b in a sh!t storm

[mmart33]

Does this total get taken out now or is this a feature you are wanting? So, you just want to see a single day(s) worth of records per employee? Does this db not let more than one pc use it at a time? You cant just a front end for the db for each user?

It is a feature already done BUT it is done with the software that is only allowed on ONE computer. The sofware is not accessible, BUT the DB is on our server that has all the tables and times entered in. There are not queries or reports there though.

[mmart33]

Field blnEventType is the In/Out indicator. So there should be an even number of records for each day, at least 2, for each employee. Your data doesn't reflect that. There are 7413 records in tblTimes, 3711 are Yes and 3702 are No. For example, employee 4 has 3 records on 02/25/2013.

Presuming In/Out must be in pairs and you fix the dataset, consider:

Query1
SELECT tblTimes.*, Format([datEvent],"mm/dd/yyyy") AS EventDate, tblTimes.lngEmployeeID, tblTimes.datEvent
FROM tblTimes
ORDER BY tblTimes.lngEmployeeID, tblTimes.datEvent;

Query2
SELECT Query1.lngEmployeeID,
Query1.EventDate,
Query1.datEvent,
(SELECT TOP 1 Dupe.datEvent
FROM Query1 AS Dupe
WHERE Dupe.lngEmployeeID = Query1.lngEmployeeID
AND Dupe.EventDate = Query1.EventDate
AND Dupe.datEvent < Query1.datEvent
ORDER BY Dupe.lngEmployeeID, Dupe.datEvent DESC) AS PriorValue
FROM Query1
WHERE blnEventType = False
ORDER BY lngEmployeeID, datEvent;

Yes, I noticed that too. We do a weekly modification (at least try to) to make sure the clock ins and outs are equal. When we open the table and people are still clocked in, then it definitely will have this. That is why I want to do a date range or day selection to view the hours report.

Thanks for the detail query ideas. I will try to get this by the end of the day and let you know how it goes. i am assumming to run the the report off of the second query?

thanks!

[mmart33]

i had done this long time back. the main headache will be if u have shifts that start and one day and end on another. typically night shifts.

how i had done it was i first took all the time record data inserted in a table
empl_cd, date, time1,time2,time3,time4,time5,time6,time7,time8,ti me9,time10 (empl_cd and date is pk, so no dups)

basicaly 5 pairs of time. people take short leave slips to run personal errands or lunch, etc. in my case i found 5 pairs of times sufficed.

1st i had a append query to add empl_cd and date.
then update query to update time1 with min([time]) of the time record table. then i appended that record ID to a logged table containing just these record ID's.
now i did the same for time2,3,4, ... EXCLUDING the ID's in the logged table. so everytime you updated the time, u again run the append query to record the ID

once you have all the empl data by date in a single row, it becomes more simple.

getting the time part of the datetime - TimeValue([datEvent])

my suggestion is if you dont have to do these, don't. the reason is people forget to log in, log out. sometimes people log in more than once, thinking they haven't log-in or out. and since it is tied with salaries, when things go wrong, u'll b in a sh!t storm

Thanks for your suggestions. I am going to try June7's ideas and let everyone know how that goes. Yes, the having to check if they clocked in or out is annoying, but having to hand calculate time cards by me, then by our accountant is redundant and even more annoying. At least when we run a report it is accurate and any outliers are the ones we need to check if there is a clock in and out issue. Also, the employees get docked 15 minutes if they forget to do this and that has helped tremendously!

Thanks again