Solved

Building a Time Clock in Access 2007

Posted on 2010-08-14
2
2,606 Views
Last Modified: 2013-11-28
Greetings --

Here is what I am attempting to accomplish....
The owner of the company I work for wants to start using an Access database to track and report employee hours.  The premise is that our employees open the database and click one button that basically enters their clock punch times in to the database.

I have created a table that holds the "time punches" every time a button is clicked on a form.
It creates a new record attached to the employee name when the button is pushed.  

The problem I am having is figuring out how to calculate the time difference between each punch.  I know how to use the DateDiff function, but I guess the problem I am having is identifying the different records in order to calculate two different times or dates.

Am I approaching this the wrong way?
Is there something that I am missing?

I am by no means an Access expert.  I know quite a bit, but I know that there is much more I do not know.

Any help or suggestions would be appreciated.

Thanks.
0
Comment
Question by:bmwalters
2 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
ID: 33439520

see if this link helps
Functions for calculating and for displaying Date/Time values in Access
http://support.microsoft.com/?kbid=210604


scroll down and look for
GetTimeCardTotal() sample function
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33440765
The issue with building a "Time Clock" in access is that having a timer run continuously uses up a "Lot" of system resources.

The other issue is allowing for Punches that will span more than 1 day.
Ex. PunchIn= 8:13PM 1/1/2010
PunchOut= 4:18AM 1/2/2010

You were not specific when you said:
    "the problem I am having is identifying the different records in order to calculate two different times or dates."
Does your table look like this:
tblPunches
PunchID (Primary Key)
EmpID (Foreign Key)
PunchInDateTime
PunchOutDateTime

Herein lies the problem.
There are many ways to identify if a Punch is an IN or an OUT.
This needs to be an established company policy, not something you as the developer decides.
Will they be punching in and out for lunch?
At what pint do you round to the nearest quarter hour?

...ect
One way is to assume that an employee will only work no more than 10 hours in a 24 hour period, and also not punch in again untill at least 12 hours later.
Ex: punch in: 1/1/2010 8AM
PunchOut: 1/1/2010 7 PM
This will means that they worked 10 hours.
Now, the next day they pinch in at: 8AM, so this is more than 12 hours after the last punch (6PM the previous day)
This means this Second 8am punch is a n "IN" Punch.

Again the logic behind doing this "Automatically will me difficult to figure out.
This can result in people getting paid for the time between days (12 hours) rather than the actual times.
Figuring Lunch In and Out is also tricky.
The biggest issue is when a person "forgets" a Punch.  Then the whole system is thrown for a loop.
With any system, there will always be something that will have to be verified manually.
There are no easy answers here.
I will tell you that many "Advanced" systems for doing this make mistakes.
ex: Daylight savings time, overtime, Night differentials, "Buddy" Punches, ...etc
Will you be needing Daily, Weekly, WorkWeek, 1/2Month, or Bi-weekly totals?
You never stated if this was a simple time tracking system or a system that would interface with Payroll.
It is also
In other words, you did not explain the "scope" of this project.
The link capricorn1 posted is a good starting point for a "Basic system, but again, without knowing your exact needs, even that system can produce errors.

Remember that there are commercial programs available for this.
One division in our company uses this:
http://www.timeclockeshop.com/index.asp?PageAction=VIEWPROD&ProdID=282
So for a small company, for less than 200USD you are ready to go.
So for possibly less than the cost of 1 days work for you (25/hr*8hrs=200)
You can have a professionally designed app (That is even Web Based) with Full support, upgrades, warranties, ...etc.

There are many things that can go wrong and you may be legally liable for these errors, ...remember Payroll and Time keeping are not things to be taken lightly.
If anybody gets paid double (or not at all), using your homegrown system, the company will be looking at you, the developer.
;-)

JeffCoachman

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now