I have two tables containing related data; both have a Participant_ID column. I've created a relationship between Table_1 and Table_2 with Table_2 as the lookup table. This is so that by setting up a Pivot Table in a certain way I can see where a value in Table_1's column is not in Table_2 (it will come up as "blank" in Pivot Table). It works fine. I also want to know the opposite, i.e. if there are values in Table_2 that aren't in Table_1. Thus, I've figured that I need to create the reciprocal relationship with Table_2 as my starting table and Table_1 as the lookup table. Whenever I try this, I get:

this relationship cannot be created because one or more columns contains duplicate values

This isn't true - neither of the columns contain duplicates, or any blanks that would be picked up as duplicates. I've checked a million times.

No matter what I do or change, I get this error. I've even tried this on completely new tables with only 1 row of data in each and thus no duplicate values, and once again, the first relationship can be created but when I try to create one in the opposite direction I get the same error message.

Yes, I know I can use other formulas to check if a value is in one table but not another, but I wanted to use a Pivot Table to also easily look at other similar fields between the two tables as well.

It's driving me absolutely crazy. In sum, why can you create a relationship between Table_1 and Table_2, but not between Table_2 and Table_1?

Image 1: The relationship in one direction can be created.
enter image description here

Image 2: The error message I get when I try to create relationship in opposite direction
enter image description here

  • I'm afraid we can't help you without knowing more details: please post some sample data on which you can recreate the issue, also describe the exact steps necessary to recreate the issue. – Máté Juhász May 7 '18 at 13:12
  • Sorry - new to this. Added links to pictures – Angela MacIsaac May 7 '18 at 13:28
  • @Angela please accept my answer if it is correct or tell me why it isn't so I can improve it. – danzel May 9 '18 at 6:12

You are trying to create a relationship loop, which is prohibited according to Microsoft's documentation:

Self-Joins and Loops

Self-joins are not permitted in a Data Model. A self-join is a recursive relationship between a table and itself. Self-joins are often used to define parent-child hierarchies. For example, you could join an Employees table to itself to produce a hierarchy that shows the management chain at a business.

Excel does not allow loops to be created among relationships in a workbook. In other words, the following set of relationships is prohibited.

Table 1, column a   to   Table 2, column f

Table 2, column f   to   Table 3, column n

Table 3, column n   to   Table 1, column a

If you try to create a relationship that would result in a loop being created, an error is generated.

  • Please include a relevant quote. This link may not work in the future. – Anaksunaman May 9 '18 at 5:58
  • @Anaksunaman you're totally right, thank you for the feedback. – danzel May 9 '18 at 6:08
  • Lol! Always nice to know I'm totally right. ;) But you're welcome. =) – Anaksunaman May 9 '18 at 6:16
  • Sad to hear this is the case, but thank you! – Angela MacIsaac May 10 '18 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.