If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Proper Table Design and Combo Box Help
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 |
#2
|
|||
|
|||
Proper Table Design and Combo Box Help
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 |
#3
|
|||
|
|||
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 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. -- 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 |
#4
|
|||
|
|||
Proper Table Design and Combo Box Help
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! "JWeaver" wrote: 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. -- 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 |
Thread Tools | |
Display Modes | |
|
|