I have two tables containing related data; both have a
Participant_ID column. I've created a relationship between
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_2, but not between