Having Relationship problems -access relationship! (Access 2000)

Topic

New Reply

This might be a noob question for you guys but

Im trying to make a one to many relationship between two tables using 4 fields and enforced referential integrity. Now if i do one field at a time it works fine but doing all 4 at same time it gives me this error:

No unique index found for the referenced field of the primary table

Also i don’t index if i wanna do many to many right?

Reply | Quote

Replies

Your multiple fields have to comprise a unique key before you can enforce referential integrity with another table. Unless you have a unique key on the One side of the relationship, there’s no way to enforce referential integrity on the Many side. Many-to-many requires a “join” table in between and that table contains a unique key from each of the other tables. I don’t understand your question about not indexing. There isn’t any way to create a Many-to-many relationship without unique indexes in the tables being joined.

Reply | Quote

charlotte you lost me.

each field is setup in the one said as unquie. But i doesnt work. What i dont understand is if i do one at a time it works just fine but more than one at a time and it wont do it.

Reply | Quote

Why would you have multiple unique keys in the table? What you need is an index that is set to unique and contains all the fields you need in that unique key and that you want to join to the other table, assuming that all the matching fields are in the many table as well. you can select multiple fields at once from the One table and drag them to the Many table to create the one to many relationship on multiple fields. If you try that and it doesn’t work, then you need to tell us what specific message you get. If there is data already in the tables, you won’t be able to enforce referential integrity unless the data permits it. If you have records in the Many table that have no match in the One table, then you won’t be able to enforce referential integrity.

Reply | Quote

thank you so much charlotte. That last post fixed ALL my problems!
<3 charlotte!

Reply | Quote