Employee Time Tracking

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I would like to create a database that will track employee times. Our
company does not have any software or badges for this and our department is
supposed to create something as sort of a pilot for employee time tracking.

Someone suggested using Microsoft Access as the program for doing this. I
know I can't be going about this the right way and I need help.

So far, I have a Master Table [Master] set up that contains all employee
times. Here are the fields that are in the table:
[Login], [Break 1 Out], [Break 1 In], [Lunch Out], [Lunch In], [Break 2
Out], [Break 2 In], [Logout]

I have created a form that launches everytime the user opens the database
and they can navigate to the appropriate form for Login, Lunch, etc. Each
form contains a ComboBox (contains usernames), a Date Field (=Date()), a Time
field (=Time()), and a button that saves the record to the Master Table.

Here are the problems I am running in to:
- Each time a record is saved from any of these forms, it creates a whole
new record in the Master Table. I would like for the Master Table to have
just one record containing all of the times instead of a separate record for
each time.

- All I want to be able to do is query when someone is late based on
requirements set for each time tracked. For example, any [Login] that is
past 8:05:59 AM needs to be on a report.

- The security in this database is awful. Any employee could go in and
select a User Name from the ComboBox and login for anyone. I'm not sure, but
I think you can set up usernames and passwords for this, however, I don't
know how.

Does anyone have any suggestions on how to better create a database for this
purpose or suggest how to repair the mess I have already created.

Thanks.
 
Chris,
It's best to ask just one question at a time. Particularly
questions that are broad in nature. Let's address the
table design issues, and you can post new questions for your other
concerns.

You should have 2 tables.
tblEmployees
tblHoursWorked

tblEmployees should have a unique key value for each employee.
That could be an Autonumber named EmployeeID. tblEmployees will contain
all the ONE information about an employee.
FName, LName, Address, HireDate, etc... etc...
tblHoursWorked would contain all the time records (the MANY)
associated to each employee by tblHoursWorked EmployeeID.
EmployeeID,TranscationID, and those fields you indicate would constitute
tblHoursWorked.
tblEmployees would be related to tblHoursWorked via EmployeeID, in
a One to Many w/Referential Integrity relationship. Show ALL in
tblEmployees
to ANY matching in tblHoursWorked.

Now... a main form (single form) based on tblEmployees, with a subform
(continuous form) based on tblHoursWorked, related to each other on the
form by....
Link Parent = EmployeeID
Link Child = EmployeeID

Now Bob Smith can go to the ONE Bob Smith record, and enter his MANY
times in the HoursWorked subform.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."




Chris said:
I would like to create a database that will track employee times. Our
company does not have any software or badges for this and our department
is
supposed to create something as sort of a pilot for employee time
tracking.

Someone suggested using Microsoft Access as the program for doing this. I
know I can't be going about this the right way and I need help.

So far, I have a Master Table [Master] set up that contains all employee
times. Here are the fields that are in the table:
[Login], [Break 1 Out], [Break 1 In], [Lunch Out], [Lunch In], [Break 2
Out], [Break 2 In], [Logout]

I have created a form that launches everytime the user opens the database
and they can navigate to the appropriate form for Login, Lunch, etc. Each
form contains a ComboBox (contains usernames), a Date Field (=Date()), a
Time
field (=Time()), and a button that saves the record to the Master Table.

Here are the problems I am running in to:
- Each time a record is saved from any of these forms, it creates a whole
new record in the Master Table. I would like for the Master Table to have
just one record containing all of the times instead of a separate record
for
each time.

- All I want to be able to do is query when someone is late based on
requirements set for each time tracked. For example, any [Login] that is
past 8:05:59 AM needs to be on a report.

- The security in this database is awful. Any employee could go in and
select a User Name from the ComboBox and login for anyone. I'm not sure,
but
I think you can set up usernames and passwords for this, however, I don't
know how.

Does anyone have any suggestions on how to better create a database for
this
purpose or suggest how to repair the mess I have already created.

Thanks.
Click to expand...
 
You are doing it wrong.
Use a single field for date and time.

Use a table with EmployeeID (foreign key related to Employee table),
TimeStamp (DateTime field) with default of =Now(), and Action (Combo box) to
select [Login], [Break Out], [Break In], [Lunch Out], [Lunch In], or [Logout].
 
Hello Chris,

I provide fee-based help with Access, Excel and Word applications. I would
like to offer to work with you to create an Employee Time Tracking database
for you. I propose creating a badge with a barcode for each employee and
then having each employee swipe his card through a barcode reader each time
he wants to record his time. When an employee swipes his badge, a record
would be automatically recorded in the database identifying the employee and
recording the time. A barcode reader is inexpensive. You could print the
badges (and barcode) on a laser printer so you don't need a special printer.
My fee would be very reasonable. If you are interested in working with me,
contact me.

Steve
(e-mail address removed)
 
Al, your feedback helps. However, I want to make sure this is correct what I
am doing based off of your feedback.

I set up my tables like this:
tblEmployees
- EmployeeID (Primary Key) - Autonumber
- EmployeeName - Text

tblHoursWorked
- EmployeeID - Autonumber
- EmployeeName - Text
- TransactionID - Text
- Date - Date/Time
- Time - Date/Time

Now, when I set up the relationship, I'm dragging the [EmployeeID] fields
together...which Join # do I choose? Also, should I enforce referential
integrity?

Do I need to put somewhere in a table the Transactions that will be taking
place (login, break 1 out, break 1 in, lunch out, etc.) or will I have these
as choices in a ComboBox in my form or something? Do I need to create
another table with all of the Transactions and link to the tblHoursWorked as
well?

Lets start with these questions and go from here.

Thanks for your help.

Al Campagna said:
Chris,
It's best to ask just one question at a time. Particularly
questions that are broad in nature. Let's address the
table design issues, and you can post new questions for your other
concerns.

You should have 2 tables.
tblEmployees
tblHoursWorked

tblEmployees should have a unique key value for each employee.
That could be an Autonumber named EmployeeID. tblEmployees will contain
all the ONE information about an employee.
FName, LName, Address, HireDate, etc... etc...
tblHoursWorked would contain all the time records (the MANY)
associated to each employee by tblHoursWorked EmployeeID.
EmployeeID,TranscationID, and those fields you indicate would constitute
tblHoursWorked.
tblEmployees would be related to tblHoursWorked via EmployeeID, in
a One to Many w/Referential Integrity relationship. Show ALL in
tblEmployees
to ANY matching in tblHoursWorked.

Now... a main form (single form) based on tblEmployees, with a subform
(continuous form) based on tblHoursWorked, related to each other on the
form by....
Link Parent = EmployeeID
Link Child = EmployeeID

Now Bob Smith can go to the ONE Bob Smith record, and enter his MANY
times in the HoursWorked subform.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."




Chris said:
I would like to create a database that will track employee times. Our
company does not have any software or badges for this and our department
is
supposed to create something as sort of a pilot for employee time
tracking.

Someone suggested using Microsoft Access as the program for doing this. I
know I can't be going about this the right way and I need help.

So far, I have a Master Table [Master] set up that contains all employee
times. Here are the fields that are in the table:
[Login], [Break 1 Out], [Break 1 In], [Lunch Out], [Lunch In], [Break 2
Out], [Break 2 In], [Logout]

I have created a form that launches everytime the user opens the database
and they can navigate to the appropriate form for Login, Lunch, etc. Each
form contains a ComboBox (contains usernames), a Date Field (=Date()), a
Time
field (=Time()), and a button that saves the record to the Master Table.

Here are the problems I am running in to:
- Each time a record is saved from any of these forms, it creates a whole
new record in the Master Table. I would like for the Master Table to have
just one record containing all of the times instead of a separate record
for
each time.

- All I want to be able to do is query when someone is late based on
requirements set for each time tracked. For example, any [Login] that is
past 8:05:59 AM needs to be on a report.

- The security in this database is awful. Any employee could go in and
select a User Name from the ComboBox and login for anyone. I'm not sure,
but
I think you can set up usernames and passwords for this, however, I don't
know how.

Does anyone have any suggestions on how to better create a database for
this
purpose or suggest how to repair the mess I have already created.

Thanks.
Click to expand...



.
Click to expand...
 
Steve. After all this time, you still don't quite grasp the concept that
these newsgroups are for free, peer-to-peer help, do you?

The newsgroups have never been, and aren't intended to be, your private
communication channel to sell your services to people who ask legitimate
questions, expecting an answer for free.

The answers you provide, when you do try to answer (instead of just
soliciting business as you did with this one) are not consistently of
sufficiently high quality that any experienced users here have endorsed or
recommended that anyone hire you; nor will they -- first, you have not shown
you are qualified, and, second, you have demonstrated over and over that you
cannot or will not abide by the published intent and rules of the
newsgroups. You, of course, claim "thousands of satisfied customers" but
none of them seem to ever post back here to "take your part" when you are
criticized.

Too bad, Steve. I'll wager you aren't going to sell your services to this
poster, either. At least, he/she has been warned!

Larry

Steve said:
Hello Chris,

I provide fee-based help with Access, Excel and Word applications. I would
like to offer to work with you to create an Employee Time Tracking
database for you. I propose creating a badge with a barcode for each
employee and then having each employee swipe his card through a barcode
reader each time he wants to record his time. When an employee swipes his
badge, a record would be automatically recorded in the database
identifying the employee and recording the time. A barcode reader is
inexpensive. You could print the badges (and barcode) on a laser printer
so you don't need a special printer. My fee would be very reasonable. If
you are interested in working with me, contact me.

Steve
(e-mail address removed)


Chris said:
I would like to create a database that will track employee times. Our
company does not have any software or badges for this and our department
is
supposed to create something as sort of a pilot for employee time
tracking.

Someone suggested using Microsoft Access as the program for doing this.
I
know I can't be going about this the right way and I need help.

So far, I have a Master Table [Master] set up that contains all employee
times. Here are the fields that are in the table:
[Login], [Break 1 Out], [Break 1 In], [Lunch Out], [Lunch In], [Break 2
Out], [Break 2 In], [Logout]

I have created a form that launches everytime the user opens the database
and they can navigate to the appropriate form for Login, Lunch, etc.
Each
form contains a ComboBox (contains usernames), a Date Field (=Date()), a
Time
field (=Time()), and a button that saves the record to the Master Table.

Here are the problems I am running in to:
- Each time a record is saved from any of these forms, it creates a whole
new record in the Master Table. I would like for the Master Table to
have
just one record containing all of the times instead of a separate record
for
each time.

- All I want to be able to do is query when someone is late based on
requirements set for each time tracked. For example, any [Login] that is
past 8:05:59 AM needs to be on a report.

- The security in this database is awful. Any employee could go in and
select a User Name from the ComboBox and login for anyone. I'm not sure,
but
I think you can set up usernames and passwords for this, however, I don't
know how.

Does anyone have any suggestions on how to better create a database for
this
purpose or suggest how to repair the mess I have already created.

Thanks.
Click to expand...
Click to expand...
 
Steve said:
Hello Chris,

I provide fee-based help with Access, Excel and Word applications. I would
like to offer to work with you to create an Employee Time Tracking
database for you. I propose creating a badge with a barcode for each
employee and then having each employee swipe his card through a barcode
reader each time he wants to record his time. When an employee swipes his
badge, a record would be automatically recorded in the database
identifying the employee and recording the time. A barcode reader is
inexpensive. You could print the badges (and barcode) on a laser printer
so you don't need a special printer. My fee would be very reasonable. If
you are interested in working with me, contact me.

Steve
(e-mail address removed)
Click to expand...

stevie is our personal troll who is the only one in the world who does not
understand the concept of FREE peer support. Like the quality of his work,
his ethics are totally questionable.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the past Christmas
period and a few gems from the Access newsgroups to show Stevie's
"expertise".


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...


Sept 10, 2009
(In respose to a perfectly adequate GENERIC solution stevie wrote)

This function is specific to the example but not generic for any amount paid
out.

Steve



Sept 9, 2009
Steve said:
you can then return all the characters in front of it with the Left()
fumction. Would look like:
Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

Steve
Click to expand...

No, it would not look like

Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

First of all, the constants are vbCr and vbLf: no quotes around them. With
the quotes, you're looking for the literal strings.

Second, you can't Or together character constants like that. Even if you
could, Or'ing them together in the InStr function like that makes no sense
at all.



Sept 22,2009
Sorry Steve, even I can see that this is a useless answer. I made it pretty
clear that "CW259" is just ONE possible value for the control.

Steve said:
Hello David,

Open your report in design view and select txtOrderID. Open properties and
go to the Data tab. Put the following expression in the Control Source
property:

=IIF([chkActive],"CW259","(CW259)")

Steve
Click to expand...


John... Visio MVP
 
I think Al gave you all the information regarding the relationship.
EmployeeID in tblHoursWorked should be LONG not Autonumber. Also,
EmpoloyeeName should not be stored in tblHoursWorked and change the name of
your Date and Time fields since these are both function names.

tblHoursWorked
- HoursWorkedID autonumber primary key
- EmployeeID foreign key to tblEmployees.EmployeeID
- TransactionID - foreign key to tblTransactions.TransactionID
- TransDate
- TransDuration - double to record the number of hours or minutes or
whatever

If you want to log actual points in time for various transactions, you can
change the TransDate to TransDateTime and omit the TransDuration.
--
Duane Hookom
Microsoft Access MVP


Chris said:
Al, your feedback helps. However, I want to make sure this is correct what I
am doing based off of your feedback.

I set up my tables like this:
tblEmployees
- EmployeeID (Primary Key) - Autonumber
- EmployeeName - Text

tblHoursWorked
- EmployeeID - Autonumber
- EmployeeName - Text
- TransactionID - Text
- Date - Date/Time
- Time - Date/Time

Now, when I set up the relationship, I'm dragging the [EmployeeID] fields
together...which Join # do I choose? Also, should I enforce referential
integrity?

Do I need to put somewhere in a table the Transactions that will be taking
place (login, break 1 out, break 1 in, lunch out, etc.) or will I have these
as choices in a ComboBox in my form or something? Do I need to create
another table with all of the Transactions and link to the tblHoursWorked as
well?

Lets start with these questions and go from here.

Thanks for your help.

Al Campagna said:
Chris,
It's best to ask just one question at a time. Particularly
questions that are broad in nature. Let's address the
table design issues, and you can post new questions for your other
concerns.

You should have 2 tables.
tblEmployees
tblHoursWorked

tblEmployees should have a unique key value for each employee.
That could be an Autonumber named EmployeeID. tblEmployees will contain
all the ONE information about an employee.
FName, LName, Address, HireDate, etc... etc...
tblHoursWorked would contain all the time records (the MANY)
associated to each employee by tblHoursWorked EmployeeID.
EmployeeID,TranscationID, and those fields you indicate would constitute
tblHoursWorked.
tblEmployees would be related to tblHoursWorked via EmployeeID, in
a One to Many w/Referential Integrity relationship. Show ALL in
tblEmployees
to ANY matching in tblHoursWorked.

Now... a main form (single form) based on tblEmployees, with a subform
(continuous form) based on tblHoursWorked, related to each other on the
form by....
Link Parent = EmployeeID
Link Child = EmployeeID

Now Bob Smith can go to the ONE Bob Smith record, and enter his MANY
times in the HoursWorked subform.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."




Chris said:
I would like to create a database that will track employee times. Our
company does not have any software or badges for this and our department
is
supposed to create something as sort of a pilot for employee time
tracking.

Someone suggested using Microsoft Access as the program for doing this. I
know I can't be going about this the right way and I need help.

So far, I have a Master Table [Master] set up that contains all employee
times. Here are the fields that are in the table:
[Login], [Break 1 Out], [Break 1 In], [Lunch Out], [Lunch In], [Break 2
Out], [Break 2 In], [Logout]

I have created a form that launches everytime the user opens the database
and they can navigate to the appropriate form for Login, Lunch, etc. Each
form contains a ComboBox (contains usernames), a Date Field (=Date()), a
Time
field (=Time()), and a button that saves the record to the Master Table.

Here are the problems I am running in to:
- Each time a record is saved from any of these forms, it creates a whole
new record in the Master Table. I would like for the Master Table to have
just one record containing all of the times instead of a separate record
for
each time.

- All I want to be able to do is query when someone is late based on
requirements set for each time tracked. For example, any [Login] that is
past 8:05:59 AM needs to be on a report.

- The security in this database is awful. Any employee could go in and
select a User Name from the ComboBox and login for anyone. I'm not sure,
but
I think you can set up usernames and passwords for this, however, I don't
know how.

Does anyone have any suggestions on how to better create a database for
this
purpose or suggest how to repair the mess I have already created.

Thanks.
Click to expand...



.
Click to expand...
Click to expand...
 
Duane,

Thanks for the info. So, how do I create this "Continuous Subform" that Al
was referring to in his post? I need this form for users to be able to pick
out their username from a list (or even type it in theirselves) and click a
button to save the timestamped record to the tblHoursWorked table.

Please let me know your thoughts.

Thanks.

Duane Hookom said:
I think Al gave you all the information regarding the relationship.
EmployeeID in tblHoursWorked should be LONG not Autonumber. Also,
EmpoloyeeName should not be stored in tblHoursWorked and change the name of
your Date and Time fields since these are both function names.

tblHoursWorked
- HoursWorkedID autonumber primary key
- EmployeeID foreign key to tblEmployees.EmployeeID
- TransactionID - foreign key to tblTransactions.TransactionID
- TransDate
- TransDuration - double to record the number of hours or minutes or
whatever

If you want to log actual points in time for various transactions, you can
change the TransDate to TransDateTime and omit the TransDuration.
--
Duane Hookom
Microsoft Access MVP


Chris said:
Al, your feedback helps. However, I want to make sure this is correct what I
am doing based off of your feedback.

I set up my tables like this:
tblEmployees
- EmployeeID (Primary Key) - Autonumber
- EmployeeName - Text

tblHoursWorked
- EmployeeID - Autonumber
- EmployeeName - Text
- TransactionID - Text
- Date - Date/Time
- Time - Date/Time

Now, when I set up the relationship, I'm dragging the [EmployeeID] fields
together...which Join # do I choose? Also, should I enforce referential
integrity?

Do I need to put somewhere in a table the Transactions that will be taking
place (login, break 1 out, break 1 in, lunch out, etc.) or will I have these
as choices in a ComboBox in my form or something? Do I need to create
another table with all of the Transactions and link to the tblHoursWorked as
well?

Lets start with these questions and go from here.

Thanks for your help.

Al Campagna said:
Chris,
It's best to ask just one question at a time. Particularly
questions that are broad in nature. Let's address the
table design issues, and you can post new questions for your other
concerns.

You should have 2 tables.
tblEmployees
tblHoursWorked

tblEmployees should have a unique key value for each employee.
That could be an Autonumber named EmployeeID. tblEmployees will contain
all the ONE information about an employee.
FName, LName, Address, HireDate, etc... etc...
tblHoursWorked would contain all the time records (the MANY)
associated to each employee by tblHoursWorked EmployeeID.
EmployeeID,TranscationID, and those fields you indicate would constitute
tblHoursWorked.
tblEmployees would be related to tblHoursWorked via EmployeeID, in
a One to Many w/Referential Integrity relationship. Show ALL in
tblEmployees
to ANY matching in tblHoursWorked.

Now... a main form (single form) based on tblEmployees, with a subform
(continuous form) based on tblHoursWorked, related to each other on the
form by....
Link Parent = EmployeeID
Link Child = EmployeeID

Now Bob Smith can go to the ONE Bob Smith record, and enter his MANY
times in the HoursWorked subform.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."




I would like to create a database that will track employee times. Our
company does not have any software or badges for this and our department
is
supposed to create something as sort of a pilot for employee time
tracking.

Someone suggested using Microsoft Access as the program for doing this. I
know I can't be going about this the right way and I need help.

So far, I have a Master Table [Master] set up that contains all employee
times. Here are the fields that are in the table:
[Login], [Break 1 Out], [Break 1 In], [Lunch Out], [Lunch In], [Break 2
Out], [Break 2 In], [Logout]

I have created a form that launches everytime the user opens the database
and they can navigate to the appropriate form for Login, Lunch, etc. Each
form contains a ComboBox (contains usernames), a Date Field (=Date()), a
Time
field (=Time()), and a button that saves the record to the Master Table.

Here are the problems I am running in to:
- Each time a record is saved from any of these forms, it creates a whole
new record in the Master Table. I would like for the Master Table to have
just one record containing all of the times instead of a separate record
for
each time.

- All I want to be able to do is query when someone is late based on
requirements set for each time tracked. For example, any [Login] that is
past 8:05:59 AM needs to be on a report.

- The security in this database is awful. Any employee could go in and
select a User Name from the ComboBox and login for anyone. I'm not sure,
but
I think you can set up usernames and passwords for this, however, I don't
know how.

Does anyone have any suggestions on how to better create a database for
this
purpose or suggest how to repair the mess I have already created.

Thanks.



.
Click to expand...
Click to expand...
Click to expand...
 
Not to mention that anyone who supposedly has "thousands of [satisfied ?]
customers" is obviously doing nothing to generate return business from the
customers that they have already serviced.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Back
Top