Joins in the Data Model - why do some fields do not work

[bearcub]

I created a relationship in between two tables and tried to create a pivot table. When I added a field from the second table in the data model I received an error message saying that I needed to create a relationship between the two tables though one existing when I open the relationships window. Could this caused because I don't have a one to many relationship - both tables have duplicate values (I have a many to many relationship). How do these relationships work in Excel (as opposed to Access)?

Do I have to create some sort of secondary table with unique values and create a unique relationship to these other tables? By creating this unique table (intermediary table) I'm think I should be able to pull fields from these other tables into a pivot table because of the Join I created using these 3 tables. Or, are they other workarounds that I could create without having to merge the tables (which is difficult to do because of the way they are set up).

Thank you for your help in advance,

Michael