Punch in and out using access database

I would like to build a database with a user interface This would allow my employees to punch in and out of their breaks. Currently they punch using a piece of paper on a time clock. This is a real paper mess and time consuming to have to review and check for late punches. I want to be able to store the punches and have the database calculate if they went over their 15 min break time. They are allowed 2 - 15 minute breaks during the course of their 8 hr shift. The goal is for them to do the punch on the computer vs a time punch system.

I need some expert advice.

Thank you,

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Simon BallCommented:
do you have a network pc or other where the employees will not be able to change the time on the machine clock?

if so then you could have a database and a simple form where they enter their name in a field, click going out or going in, and the database stores a reocord for the event with a time stamp. ( which i think is ussing the NOW() function based on local machine time).

you can use format(now(), "DDMMYYY") and format(Now(),"HHMMSS" to seperate current day/time into two fields so you can order by user and date

you can then query the table later order by name and time ascending.

there are time rlated functions you can use as well to work out whether they were out for more than 15 mins...

the only problem i can see is that this way, one member of staff could log all thei friends in at the same time...thumbprint scanner ( on those snazzy new laptops) would be a good way to go.
As Sudonim mentioned, there are security issues involved in timeclock systems, as well as legal and accounting issues. A good timeclock system can directly interface into your payroll system and save you time, money and manpower. In addition, it can give you a solid audit trail that unauthorized employees can't readily tamper.
Simon BallCommented:
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

culwatrncaAuthor Commented:
Sorry guys, Was in meetings all last week and could not get back to you.

How do I create a button that captures the punch in and out so the current time is stored in the a table?
culwatrncaAuthor Commented:
I need to get this thing created quickly. The clocks on paper are just overwelming. If someone wanted to created something for me that worked, to get me started I would be more then willing to up the points.
Simon BallCommented:
are you going to do both in and out from the same form / buton, or are you going to have a punch in button and a punch out button on the same form...?

will the user be entering their name / id / initials?

if you have a textbox on the form whose value is now() then the button needs to append reset that textbox = now () make it current time, and then run an append query which picks up the time and date and username from the form and inserts it into a table

if the form is called frmtimeclock for instance..
and the user textbox is called txtuser, and the current time is called txtnow
and there is a txtfield called txtInout

'the relevant button sets the in out flag,
'this procedure runs the rest of the code for either button
txtnow.value = now()
'and set the in_out field for the relevant button - coming in / going out

if txtuser.value = "" then
msgbox "Please enter a user name / ID"
goto Exit_this_sub
end if

docmd.openquery "my_timesheet_append"


then the onclick event for each of the buttons would be:

txtin_out.value = "out" (or "in")

the append value would be [forms]![frmtimeclock]![txtnow].value
and the target table is tbltimesheet

here's an example sql for the query:

INSERT INTO tbltimesheet ( [user], [timestamp], in_out )
SELECT [forms]![frmtimeclock]![txtuser].[value] AS form_user, [forms]![frmtimeclock]![txtnow].[value] AS form_now, [forms]![frmtimeclock]![txtin_OUT].[value] AS form_inout
FROM tbltimesheet;

I have mocked this table / form / query set up in an mdb if you want it emailing post me your email .

Simon BallCommented:
you could go much further by having a list of valid user id's, and forcing the selection of one of these from a dropdown before the in or out button is pressed.
Simon BallCommented:
error - i hav had to add a tbluser with valid users in order to get the append quer to work, for a valid user.

one of the other experts who writes sql direct into their DB could do it wihout using .runsql, and an append Values query, but this way is just as good in this case.

the sql query will be like this:

INSERT INTO tbltimesheet ( [user], [timestamp], in_out )
SELECT tbl_users.User, forms!frmtimeclock!txtnow.value AS form_now, forms!frmtimeclock!txtin_OUT.value AS form_inout
FROM tbl_users
WHERE (((tbl_users.User)=[forms]![frmtimeclock]![txtuser].[value]));

and i have converts txt_users into a como box using format:changeto:combobox, and the row source is

"SELECT tbl_users.User FROM tbl_users; "
culwatrncaAuthor Commented:

Which would be easier to have, one button for clocking in and out or to have two seperate buttons "IN" "OUT"?  I was thinking one button, or possibly they could enter their name or employee number and enter vs a in and out button. Once a Clock out time was entered the next punch would be a clock in time for the same code. The database would be able to see that a out punch was already entered on that day so the next punch would be a in punch.
My people are allowed 2 breaks. Sometimes depending on how buys they are they may only take one break. Each break is 15 min long.

I would like for them to maybe enter their name or as they type there name it will display automatically from a list so the spelling is correct. If you have a better idea I am open for suggestions.

It would look something like this:  
                                                              Break1                    Break2
Name           EmployeeID     Date              Out         In            Out        In
John Doe      271022          5/25/2005      10:00a     10:15a     4:00p     4:15p

If they only take one break then nothing will show for break 2.
Simon BallCommented:
i am sending you what i had done so far.  its got two buttons, one sets the value to in, one to out, then they both run he same code to check if a username as been entered, run the append query, and then clear the boxes for the next user.

what i am sending should get you started, then you can look in to the further development you have mentions.

you will need to enter the users daa in the table tblusers, and you'll need to think about how you want to spec your report.
culwatrncaAuthor Commented:

Thanks for your help on this.

Question regarding the append query, what is this for. Am I supposed to run this or does it run automatically?

Simon BallCommented:
the append query is run by the button to push the data from the form into the table.

have a look at the design mode of the query, and check out the code behind the buttons...:-)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Simon BallCommented:
nice one
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.