RE: Proper Table Design and Combo Box Help

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 time*** 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.
--
JWeaver


"Fred" wrote:

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" wrote:

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