I am new to access, and am trying to create a time clock that my employees can punch in and punch out. Using a weekly view of a calendar. trying to get code that will have the command buttons punch in and out for the day of the week. ie, today is weds, punch in and out will associate with weds. the time value is going into a text box. Also, employees may punch in and out, up to 3 times a day, so i will need it to go from punch in 1, to punch in 2, etc. but keep the value in punch in 1. can anyone help with this?

Welcome to Utter Access!

There are a lot of examples in the Code Archive for picking dates and times from calendars.

Also, you might want to check out the demo from this previous thread to help you get started.

Hope that helps...

where do i locate the archive?

Click on the Forums Index menu on the top and in the second second section, go to Access Code Archive.

You can also use the Search feature at the top right of the page.

Hope that helps...

wasn't able to find anything on the archive. is it possible for you to take a look at what i have going on?

Hi,

I can't say that I can fully envision what you're trying to get in the end, but I would make a comment on your table structure.

Having fields named FieldName1, FieldName2, FieldName3, etc... indicates a non-normalized data structure.

Before you start designing forms, you should normalize the data first.

In your case, I recommend something like:

tblEmployees
EmployeeID, pk, autonumber
LastName
FirstName
etc...

tblTimeClocks
TimeClockID, pk, autonumber
EmployeeID, fk
PunchIn, date/time
PunchOut, date/time

In the above structure, the tblTimeClocks table can store as many (or as few) times each employee clocks in or out. It would also account for any employee clocking out after midnight (next day).

Hope that helps...

Calendar, schmalendar....

Create an app with a tabel like Emp#, Name,Punch, In/Out and processed (T/F)

from your form populate the punch data with the now(0) function

Then you can run queries where you consolidate the ins and outs into a single table (allowing a supervisor to edit), subtract the outs from the ins (time-time yeilds number data), sum the numbers and voila!

I use USB flash drives as tokens to allow clock in/out after hours remotely from the time clock computer. 2003 still uses the older security scheme which allows me to run apps from an autoini file with usernames and passwords easily.

Anyway, it can be done this way & I'm sure that there are many others, too.

I am trying to be able to have employees punch in and out, and pull a report 2 times a month to show there time. like i said, i am new to access...

To help you learn more about Access and database design, some good reference posts (in no particular order) are:

• Database planning tutorial Part I
  
• Database planning tutorial Part II
  
• argeedblu's FAQ on Design Strategies
  
• Noah's FAQ on Normalization
  
• Jerry's FAQ on Normalization
  
• GroverParkGeorge's PDF on Normalization
  
• Jerry’s FAQ on Fully Qualified Naming
  
• Jerry's FAQ on Glossary
  
• Danny's FAQ on Reserved Words
  
• Autonumbers - What They Are / Are Not
  
• Primary Keys - Natural vs Surrogate
  
• Naming Conventions - Hungarian Notation
  
• Crystal's ACCESS Basics for Programming
  
• Database Answers (free models)
  
• MSKB 283878: Description of the database normalization basics
  
• The Evils of Lookup Fields in Tables

DBguy,

thanks for the advice. ok, so i am starting over on this to the suggested format of the tables that you suggested.
my question is how can i have the command button formated so that when my employees punch in and out 2-3 times a day, so that it will keep the value from the first punch, and add the time from the second punch?

Also, i am not sure how i can add the FK to the second table.

Thanks for your help.

Hi,

Have you looked at the demo in the link that I provided in my first post? I believe it answers some of your questions. If not, let us know. I will also try to look for a better demo for you.

Hope that helps...

I haven't checked it out, but i will later. thanks for all your help, i really appreciate it!

You're welcome. We need to make sure your data structure is normalized. So when you get a chance, you might want to post an empty copy of your db for someone here to comment on your design. Good luck.

As always, we should be careful a t the advice we throw out here.


for starters, don't use special characters like the # sign in your field names, also, be wary of reserved words like Name. Also, In/Out has the special character again...

I am sure this was just a general idea without getting into specifics but newer users may not be aware of these pitfalls...

You could try something more like:

tblEmployees
EmpID (autonumber) PK
LName (text)
FName (text)
MI (text)
UName (text) *
other Employee specific attributes

*The UName field will enable you to log the right times to the right employee based on who logged into the computer

tblClockTimes
ClockTimeID
EmpID
ClockTime (Date/Time)
ClockActionID (Long Int)

tblClockActions
ClockActionID
ClockAction (text) store one record for In one for Out

Alternatively, you could just have InOut in tblClockTimes and set it as a Yes/No field

Hope that helps some.

J

DBguy,

i downloaded your attachment from the previous thread. gives me a good idea of where to go. my question is how would i be able to make that so it shows the punch in and out several times for the same day? or would they just have to start a new record?

Yes, starting a new record is what I would suggest if the number of times they clock in varies. That would keep the data normalized. You would then be able to perform queries easily to get the information you need.

If anyone else has a better idea, I'm sure they will tell us. For now, good luck with your project.

DBguy,

i don't think that the attachment i downloaded functions. i believe i am downloading what the other person had. still has all his notes on it, and nothing seems to work. i am going to post mine, and if you can take a look at it and let me know what you think. the biggest thing i am having an issue with right now is the command button to timestamp on different fields, or to change the record after the time in, and time out are done. am i going to have to come up with a query for that? and if so, can you make a suggestion? again, thanks for all your help.

Hi,

I don't have time right now to modify your structure, but I would suggest that you make some modifications in your design.

First, I would recommend that you use an autonumber primary key for all your tables. For example, the Title table.

Second, it is recommended (by experts) that you don't have lookup fields at the table level. For example, the Employees table has a lookup in the Title field; the TimeClocks table has a lookup in the EmployeeID field.

Third, I'm not sure that you need the table EmployeeShifts (unless you wanted to keep a history of a person's shift assignment). You should be able to just add a Shift field in the Employees table to assign an employee to a particular shift.

I will see if I can put together a quick demo for clocking in and out based on your requirements. Each entry will be a new record to follow normalization rules.

I'll try to get back to you soon...

Hi,

Attached is a quick and dirty demo for you on punching a time card in or out.

Hope that helps...

DBGuy, I got bored at lunch...I sued to have a really great little Time Clock app but have no clue what i did with it..here is something I threw together...

Would love to see what you come up with as well...

McCrotty5, enjoy...

I see you went with a in and out in one record...I went with one record for each action...both good approaches I suppose...your's might be a touch easier to figure out if someone hasn't clocked out when they should have....

Guess McCrotty has plenty of good examples now

J

Thanks, Jason.

I haven't looked at your approach but I'll make sure to do that. I have tried separate records before with an extra field to indicate "In" or "Out" but I had a real problem sorting out the time duration between each matching TimeIn and TimeOut (especially if the app allowed consecutive Time In or Time Out).

Cheers...

Yep, I could see the headache in figuring out time spent at the office between each clock in...hmm...maybe I'll get bored again and mess around with that later...Time Clock apps are always handy to have around

thanks for all your help guys. DBguy, with your application, when i put on USL, will that automatically place the employees information into the lookup field? i really would only want the lookup field available to the administrator. also, how can i make it so there is an edit button, or link, that would be only available to the admin to adjust a forgotten or incorrect punch in or out? really, thank you guys for all your help.

I don't think applying ULS will automatically put the user names on the form for you. Besides, I don't normally use ULS because of all the other setup procedures that comes with it. I usually employ my own security features to the database. There is a good demo in the code archive on how to create your own security features for your database. Let me know if you can't find it.

DBGuy.. this is fantastic, i cant tell you how much this helped me.

Hi,


Thanks for your comment. Glad to hear you were able to use the demo in your project.

Good luck!

Hey DB Guy, I have used the code from your test in my project, with minimal modification so it should be no problem to to make changes.. here is my question

Sometimes employees do not check in at the end of the day when they leave (actually, about 25%). Is there a way other than manually checking each one "out," to check out all employees listed as still "checked in" in one click... So that the gate guard can just click "check out all" at the end of the day so that everyone is listed as "Checked out" the next morning?

PS. it won't allow me to upload the file so just use the demo you submitted as code. Your genius example really doesn't my 2 cents, i just added functionality for my purpose but i did not modify the structure.

Hi fijijames,

Yes, that's possible. Try the following steps:

1. Add a command button on your form called "cmdCheckAll" and labeled "Check-In All Employees"
2. In the Click Event of this button, select [Event Procedure] and use the following code:

Dim strSQL As String

strSQL = "UPDATE tblTimeClocks SET DateOut = #" & Now() & "# WHERE DateOut Is Null"

CurrentDb.Execute strSQL, dbFailOnError

3. Save the form and try it out.

Just my 2 cents...

Db Guy, i do believe that your are the MS Access prophet! Amazing, works perfectly; and i cant thank you enough.. especially for getting back to me on Sunday of all days. This was the only thing holding me up, the project was great except for this (now) non-issue; previously i was going to have to make 31 copies of the DB and have the gate guard use a new one for each day as a temp work around... glad that i don't have to do that anymore.

Hi fiji,



Good luck with your project.

It's been a while since i have been on UtterAccess but i thought it might help me solve another problem like it has been so great at before. The program has been working now for months with no problems i just want to use SQL to simply the removal of select employees.

What i need is really simple: when i select the employee's name in the combo, one can then click the "Delete" button, and delete the employee. My deal, it doesn't work. Can anyone help me please?

Here is what i got so far:

cmdDelete- is the button
tblEmployees- the list of each employee. The primary key is EmpID(number), not the persons name which is what i want to reference, through a combo box, to make the delete.
cboEmployees- is the combo box that displays all the employees names.
Name- is the object that holds the employees name in tblEmployees.

Here is the code:

Private Sub cmdDelete_Click()
Dim strSQL
Dim cboEmployee As String

If MsgBox _
("Do you wish to deleted " & Me.cboEmployee & "?", vbYesNo) _
= vbYes Then
strSQL = "DELETE FROM tblEmployee WHERE Name = " & cboEmployee & ""
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery
End If
End Sub

What i need is really simple: when i select the employee's name in the combo, you can then click the "Delete" button, and naturally delete it. My deal, it doesn't work. Can anyone help me please?

Hi,

Welcome back! What would happen if you have more than one employee with the same names? It is better to use the record's ID field to make sure you are deleting the correct record. If you agree, you will need to fix your combobox so that the bound column is the ID field.

Just my 2 cents...

True, but what i have beside this simple interface are locked textboxes displaying their Supervisor and their phone numbers. Luckily those are functioning. So what do you think?