How to Relate Two Fields in One Table to One Field in Another

[BikeToWork]

I have a table (tblMedia) with fields, "AssignedTo" and "ReturnedBy" which both relate to the StaffID field in tblStaff. Access won't allow creating two joins from tblMedia for both fields to tblStaff on StaffID. What I've done for now is create a tblStaffReturnedBy, which is a clone of tblStaff. However, this is not normalized, since the same data is stored in two different tables. Could anyone advise me on how to normalize this?

[Seth Schrock]

If you create the one relationship and then create the second relationship, it should end up adding another table to your layout with the same table name with a _1 appended to the end. You can't create them at the same time, but you should be able to create them separately.

[BikeToWork]

Seth, thanks for the response. I did try creating the relationship for the two fields to the StaffID field and the AssignTo field from tblMedia was related to tblStaff while the ReturnedBy field was related to tblStaff_1. I'm just not sure what this means in terms of the actual relationship. Is ReturnedBy related to tblStaff_1.StaffID or tblStaff.StaffID? What I want is to have both AssignedTo and ReturnedBy joined to tblStaff.StaffID. Thanks in advance for any advice.

[Seth Schrock]

The relationship is the same, but because it is two separate relationships between the two tables, Access has to differentiate between the two. The relationship works the same. I'm currently working on a database that has items getting handed out. Both the "Giver" and "Receiver" fields are linked to the Employee table. The relationship layout shows tblEmployee and tblEmployee_1, but the relationship remains the same for each table. It is hard to explain, but it works.

[BikeToWork]

I was able to create the database relationship as you mentioned, Seth. Now how do I deal with this in queries? Do I need to create a tblStaff_1 in the queries to link the ReturnedBy field to? There is no tblStaff_1 in the database. Thanks for any advice on this.

[Seth Schrock]

You would just add tblStaff twice, drag the link across and then make sure you choose the your fields from the correct one. Going back to my example, if I had tblEmployee related to "Giver" and tblEmployee_1 related to "Receiver", I would have to get fields from tblEmployee if I wanted information related to the "Giver" and from tblEmployee_1 if I wanted information related to the "Receiver". It acts like two separate tables, but it is really only one.

[BikeToWork]

I see what you mean, Seth. It is really just using an alias for the table name and joining the 2nd field to the field in the alias table. It sure seems like a convoluted way of handling it but I can understand why Access doesn't allow 2 fields from one table to join one field in another table. Do other RDBMS programs act the same way with this situation?

[Seth Schrock]

An alias is a really good name for it. It may seem convoluted at first, but you get used to it. Thankfully, it doesn't happen all that often.

I have only briefly played with MySQL and SQL Server, so my knowledge of other RDBMS programs is limited, but I haven't found a "relationships" page in MySQL. SQL Server uses the same method, but this is no surprise as Microsoft did both SQL Server and Access. Personally, I can't think of a better way to do it, and as I said before, you get used to it and it works pretty well.

[zmbd]

I think you two finally got there:

IN the relationship window you may see
tblStaff
and
tblStaff_1

For Access these are the same table - You're not going to be using [tblStaff_1] in your SQLs.

In fact, go back to the navigation panel, look at your table list... you will not see "tblStaff_1" unless you added it by hand. Create a query, in the add tables dialog, once again you will not see the "tblStaff_1" unless you added it by hand.

All Access is doing in the relationships window is an "Accounting" trick to help the user, and software, keep track of the relationships between the same two tables:
(thus)

1) [tblStaff]![StaffID] :1:M: [tblMedia]![AssignedTo]
2) [tblStaff_1]![StaffID] :1:M: [tblMedia]![ReturnedBy]

and yes, Access added an Alias
and yes,all RDMS do basically the same thing - they may just show it differently.

Personally, it would be visually cleaner to have two lines with a common starting point; however, I can understand how that could get messy if the relationships were different in the integrity settings.

In any case... BikeToWork... you can get rid of that extra table and re-normalize your database :)