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