3 empoyeeID to 3 different fields to one Table

[dipzta]

Hi.This one is a hard nut;I want to fill out a Workshop order. (car repair for example)I then use the WorkOrderTable and also have an EmployeeTableSo, this order will be received by one empoyee, it will be designated to one employee(mechanic), and it will be signed out by one employee when the workorder is done.(The "one" employee can be different persons from the EmployeeTable)So how I put up the relationships with these conditions and tables?(I believe I must have a junction table, but how do we connect these?)Or can I just relate EmployeeID from EmployeeTable 3 times to 3 different fields in WorkOrdreTable ?Thank you

[CJ_London]

you do this

just relate EmployeeID from EmployeeTable 3 times to 3 different fields in WorkOrdreTable

to do this, draw the relationship line. After the first one you will be prompted with 'a relationship already exists'. Click no to create the new relationship. An 'alias' table will appear of your employee table (suffixed with _1). This is just the way these multiple relationships are displayed

you can use a junction table - and probably better if there is a change in your procedure (for example work needs to be signed off by more than one person, more than one mechanic can work on the vehicle, etc)

to have a junction table you would also need a table for 'roles', then your junction table would look like

WOrderFK....RoleFK....EmployeeFK
1.................1...........3
1..................2..........4
1.................3...........5
1..................2..........5

assuming role2 is mechanic, this says 2 mechanics worked on the works order, one of which signed off the work

[dipzta]

Thank you!I am a 1 week noob, so now I will study this answer and try it (PS: I did earlier tried this "just relate EmployeeID from EmployeeTable 3 times to 3 different fields in WorkOrdreTable" but it didnt work. I guess I did it wrong)Edit: I tried again, and now it worked! The table in the relationship window cloned itself with the _1 _2 etc suffix as you said, so then I know I was on the right track Thanks!