locked
Trouble creating a lookup relationship between two tables RRS feed

  • Question

  • Hi,

    When trying to create a relationship between two tables I get the following error message, "The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values". 

    However there are no duplicates in the selected related lookup table. I've used a calculated column to check for duplicates and there don't seem to be any.

    Does anyone know of a solution to this?

    Monday, January 12, 2015 7:45 AM

Answers

  • I'm sorry if this should sound dumb and obvious, but this is where I'm sometimes making mistakes:

    • Check if your logical primary key in your lookup table is really unique including null values (if you're using count in your calculated field, nulls will be ignored)
    • Check if lookup is connected thru its logical primary key (go Design\Manage Relationship - graphic interface can be confusing sometimes)
    • Check if relationship is defined in the right direction (same place Design\Manage Relationship)
    Monday, January 12, 2015 8:35 AM

All replies

  • I'm sorry if this should sound dumb and obvious, but this is where I'm sometimes making mistakes:

    • Check if your logical primary key in your lookup table is really unique including null values (if you're using count in your calculated field, nulls will be ignored)
    • Check if lookup is connected thru its logical primary key (go Design\Manage Relationship - graphic interface can be confusing sometimes)
    • Check if relationship is defined in the right direction (same place Design\Manage Relationship)
    Monday, January 12, 2015 8:35 AM

  • Thanks for your answers Michael, I've tried the relationship both ways just in case and I'm fairly sure it's the right logical primary key and there are no nulls. If I remove all the columns in the selected related lookup table except for the primary key the relationship works. I've also tried it with 3 columns and that works. I'd like for the lookup table to have the full set of columns though.

    Tuesday, January 13, 2015 12:42 AM