Employee Time Tracker


C

Chris

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

Advertisements

S

Steve

Chris,

I gave you a proposal for a barcode Employee Time Tracker database that
would work very well for what you want to do at your 12/29 post. Have you
looked at what I proposed? Are you interested?

Steve
(e-mail address removed)


Chris said:
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.

:

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top