Relationship Question

[doucecool57]

I have a database with a table containing PK Autonumber with information on the different staff at my company. I have another table that holds data on "incidents" that have occured. I need to build relationships between my staff and the "incident" table but have multiple fields in the incident table that need to be associated with staff.....Initiating Staff, Involved Staff, Report Writer (All fields are to be populated using my "staff" table). I guess I'm having trouble properly building this relationship.

 

[doucecool57]

To simplify, I need to assign multiple relationships between my "staff" table and "incident" table.

 

[xenou]

For many to many you can use a linking table:
---------------------------------------
| IncidentID | StaffID | Category     |
---------------------------------------
|          1 |      10 | Initiating   |
|          1 |      11 | Involved     |
|          1 |      12 | Involved     |
|          2 |      10 | Initiating   |
|          2 |      12 | Involved     |
|          3 |      10 | Initiating   |
|          3 |      11 | Involved     |
|          3 |      14 | Involved     |
|          4 |      11 | Initiating   |
|          5 |      14 | Initiating   |
|          5 |      14 | ReportWriter |
---------------------------------------

The table is linked to Incidents on IncidentID (one to many) and it is also linked to staff on StaffID (one to many). The table has a composite Foreign Key of all three fields: IncidentID-StaffID-Category. You will have to use this table in your joins but if you set it up in relationships with the proper relationships it is usually quite quick to construct your queries in the query by example grid.