Solved

Building a Time Clock in Access 2007

Posted on 2010-08-14
2
2,658 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

948 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

22 Experts available now in Live!

Get 1:1 Help Now