One-To-Many Relationship Error

[tgroneck]

I am trying to create a one-to-many relationship between these two tables. I want to be able to access the 3 fields on the [Processors] table within reports based on [AllItems]. [AllItems] is a listing of account activity where the [AccountNumber] repeats. I have every field set as the "Primary Key" on [AllItems] as that is the only way to avoid importing duplicate data. I am getting the error: "no unique index found for the referenced field of the primary table"


Picture of the relationship screen is attached in the .zip

 

[Cronk]

That's a first for me. I've never seen a primary key based on every field like this.

To fix your problem, add a new field call AllItemsID type Autonumber and make it and only it, the primary key. Then your foreign key field, AccountNumber will not be part of a composite primary key.
​

 

[tgroneck]

That's a first for me. I've never seen a primary key based on every field like this.​

To fix your problem, add a new field call AllItemsID type Autonumber and make it and only it, the primary key. Then your foreign key field, AccountNumber will not be part of a composite primary key.​

I have to have the composite key to avoid duplicate data. I have users importing excel spreadsheets with data and need this to avoid duplicate imports. I can, however, remove [AccountNumber] from the composite key on [AllItems] without any effect to the data integrity. I am still getting an error when trying to enfore referential integrity though.

 

[Cronk]

I understand you need to have a composite unique key but I've never had need to apply uniqueness to the numeric data. Anyway...

If you can't apply ref. integrity, then obviously your existing data does not meet it.

You could try "relaxing" the integrity rule by removing fields one at a time to try to track down the double/multiple entry causing the problem. Or maybe use total queries to find where the problem is.