?
Solved

Punch in and out using access database

Posted on 2005-03-29
14
Medium Priority
?
1,219 Views
Last Modified: 2012-08-14
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,

Culwatrnca
0
Comment
Question by:culwatrnca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
14 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 13660112
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.
0
 
LVL 2

Expert Comment

by:unicorn1824
ID: 13664670
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.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 13850515
abandoned?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:culwatrnca
ID: 13856487
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?
0
 

Author Comment

by:culwatrnca
ID: 13856495
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.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 13856696
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
PUBLIC SUB APPEND_INFO()
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"
txtuser.setfocus
goto Exit_this_sub
end if

docmd.openquery "my_timesheet_append"

EXIT_THIS_SUB:
END SUB

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

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

--------------------------------------------------
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 .



1
 
LVL 15

Expert Comment

by:Simon Ball
ID: 13856703
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.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 13856738
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; "
0
 

Author Comment

by:culwatrnca
ID: 13871576
Sudonim,

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.
0
 

Author Comment

by:culwatrnca
ID: 13871624
Sudonim,


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

My email address is.

culwater@yahoo.com.

Thank you,

Walt
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 13873934
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.
0
 

Author Comment

by:culwatrnca
ID: 13889113
Sudonim,

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?

Walt
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 1000 total points
ID: 13893736
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...:-)
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 13915756
nice one
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

800 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