structuring my database and linking tables

[paintacar]

Sorry I am learning how to set up my tables. I have a question about how I should configure my tables. Lets say I have a table for employees and it includes office staff and technicians with ID number for each, so really it has fields basicly to store their names and direct phone lines for office staff along with the employee's ID number. Then I have a table with repair order number and item being serviced along with who is the office staff that is assigned and who the technician is servicing the unit and the status of service and the date the unit went into service and the date it is scheduled to be out of service. Since each unit can have more than one technician assigned to it and each technician and/or member of office staff can have more than one unit assigned to them, should I have one table for office staff and one table for technicians and one table for service status and one table for service dates? I originally thought I could have an employee table to hold contact information and names and enter the names under "office staff" or "technicians" depending on their position. and just use that table to supply the dropdown list to enter the "office staff" and "technician" fields in the main repair order table and also store the date information in the repair order table as well. If I seperate the tables wouldn't it still have to have a field in there to say the same thing ? or at least provide the same information that I was going to store there anyways? I had also planned on storing the time that the "technician" starts and stops servicing the unit. should I store that in a seperate table as well? or in the same table? because it is specific to that specific repair order? and if I am to store it in a seperate table then how do I join it? Suggestions are greatly appreciated.

[Keith Hubert]

Re: structuring my database and linking tables

You need a Header table for the Service Order with lookup fields to table where single data is required and child tables for multiple records. So, as you want more than one technician to service then you will need to have a child table linked 1:N with a lookup field to the technician's table. In each of these records you would also keep the time that the "technician" starts and stops servicing the unit.

Have a look at AlphaSports Invoice form. It might take a little imagination to apply to your situation but the answer is there.

[DaveM]

Re: structuring my database and linking tables

I did this one years ago. Keith's way is one way. I made the invoice table with fields for 2 service writers, 2 in the office and 4 tech's because of 1 for body, 1 for paint, 1 for finishing, 1 for cleaning(maybe a couple of spares). You don't always use all, but they are handy to have. each of the above fields were lookups to 1 table for employees that had a code for their job function. That code was used to filter the lookup. I had a couple other tables too, but don't remember all of them. This all tied into the payroll(seperate app). The inv also was used by mechanical repair and sales.

invo = invoice
empl = employee

(I like short names)