Proper Table Design and Combo Box Help

[JWeaver]

I have 3 tables in my database. The Employees table contains information
regarding each employee that are included in the Transportation Hours table
and the Payroll table. Some information is repeated in each table and I
believe that it would better if some fields were removed from some tables and
relationships set up but I am not sure that I know the proper way to do this.
Also, I want to be able to use combo boxes to automatically fill in
information in the Transportation or Payroll table based on the Employee
Number selected. I tried this before but encountered problems.

Can you please look at my table structures below and let me know if they are
set up correctly or what changes I should make?

Table: Employees
Last Name - Text
First Name - Text
Home Dept - Text
Emp (PK) - Text (set up as a text field because some begin with a zero)
Hire Date - Date/Time
Rate - Currency

Table: Transportation Hours
ID1 - Autonumber
Last - Text
First - Text
Emp # - Text
Pay Rate - Currency
Hours - Number
From - Date/Time
To - Date/Time
PPE Date - Date/Time

Table: Payroll
PPE Date - Date/Time
Last - Text
First - Text
Emp # - Text
Pay Rate - Number
Client First - Text
Client Last - Text
Program - Text
Contract Hours - Number
Contract Date - Date/Time
From - Date/Time
To - Date/Time
Hours - Number
ID - AutoNumber
Dups OK - Text
Notes - Text

Thank you so much for taking the time to look at this for me.

 

[Fred]

Dear JWeaver

I noticed that nobody answered. To allow people to really help you on a
fundamental DB structure question, you need to explain the real world stuff
that is getting databased. Explaining your current tables is not a
substitute for that.

You might want to repost.

 

[JWeaver]

I use this database for payroll every 2 weeks.
* The Employee table is used to hold the information regarding the
employee's names, IDs, and rates of pay.
* The Payroll table is used to keep track of each employee's hours
worked. Employees report their hours on a timesheet and sometimes will
submit more than 2 weeks worth of time at a time (they have forgotten to send
in a week and double up the next week). The hours that are submitted since
the last payroll are entered into the Payroll table and then a report is ran
that lists details about these employees and given to our payroll department
for processing.
*Some employees are paid for mileage due to the type of work they do.
They submit Forms each week so that they can be paid for this mileage. The
mileage from these Forms is entered into the Transportation table and a
report is then run and given to our payroll department as well.

 

[Fred]

Hello JWeaver,

I think I know enough about your application to give some thoughts.
Probably not enough for them to all be optimal or right. Either they are
just a few thoughts; there are a lot of specifics that you'll need to decide
and handle.

I'm assuming that it's not important to try to consolidate the
transportation pay and pay for work into the same table & report.

Your text says that you are reporting hours to the payroll department, yet
your fields seem to indicating that you are also calculating pay ($). ?????
The answer to that question will make a big difference.

Any information (except for the employee number) that is somewhat constant
for an employee should be only in the employee table and removed from the
other 2 tables. Of course this means that you shouldn't and won't be loading
this other data into your two pay table. Your forms, reports and queries
will just display the employee information whenever it's needed. Then link
those other two tables to the employee table on the employee number.

In a form make a dropdown list to show the employee name and number and load
the employee number.

Again, your reports and forms will show all of the needed data from the
three tables at the time that it's needed.

This is just a few notes on a big topic that you have there. Good luck!