Multiple name ID's link to same Names table

[hunoob]

Hi there Everyone!

I have a very big problem, and I do not know how to solve it. I have a risk table containing risks and risk owners (many owners for one risk), meaning that I have two risk owners columns. What I would like to do is to connect both risk owner columns (containing the name IDs) to the Names table. But Access does not allow to connect more than 1 column to another. How can I get past this problem? Please help as I am really stuck!

Please see the attached file to understand better my problem.

Thank you in advance for your help!

 

[Mihail]

DO NOT duplicate the posts.
This thread should be enough to ask everything in order to design your database.

 

[hunoob]

Hi Mihail!

Thank you for your reply in my other post.

I do no think that this is a double post. It is a totally different problem. The topic is also different. And this problem is still not solved.

 

[Pat Hartman]

When you have more than one of something, you have many and many means a new table. You even said you have many owners for one risk. How are you managing this with only two slots for ownerID?

Lecture over. I will answer the question since there are valid uses for multiple relationships between tables. I use them all the time since I use a single table to hold all my small lookup lists and so any given table could have several lookup values that need to join to the single lookup table. Just add the lookup table to the grid (query or relationship) as many times as you need to. Each addition after the first one will be suffixed so the table will have a unique name. So, MyTable, MyTable_1, MyTable_2, ...
Draw a join line from the main table to the individual lookup tables to create the independent relationship. So, tbl1.fld1 --> MyTable.fld1, fbl2.fld1 --> MyTable_1.tbl1, ...

 

[hunoob]

Hi Pat Hartman.

Thank you for your reply. I have multiple owners but in the trial file I just put two. I thought if I can manage with two I will be able to manage with more than two as well.

As I am a beginner it is hard for me to understand clearly your help. Could you please somehow attach an example database of what you are saying. It would help me enormously.

Thank you in advance for help.

 

[hunoob]

Hi Everyone!

I have solved the problem! What I did is I created a junction table. In the junction table I put the RiskID and RiskOwnerID codes. And connected them to the relevant table ID fields. This way I can have many owners to many risks. Thank you for your help!

 

[Pat Hartman]

That is exactly the correct solution. So just file away my description of how to link one table to another more than once for if you ever need it.