Indexing Mucks up Relationships...

[SaraMegan]

Anyone have any idea why creating an index (no duplicates) on a field in my db screws up my relationships? There were already no duplicates. I can provide more information if it's needed, but I thought someone might have a thought...

The field in question is a foreign key to another table. (I know it's a one-to-one, but it's set up correctly, I promise!! )

Thanks in advance. Again, happy to provide more info if needed.

--Sara

 

[pdx_man]

If this was a table on the right-hand of a relationship, it may be changing from a one-to-many relationship to a one-to-one. You have changed the rules of the structure mid-stream. Don't get mad at Access. (Although there are dozens of other reasons to ...)

 

[SaraMegan]

Thanks for your relply, pdx_man!

Oh, I'm not mad at Access... Just didn't know if there was a quick fix or something else I could do.... Is there a way to set up a validation rule that says "no duplicates?"

Thanks again

--Sara

 

[pdx_man]

Not an efficient way, no, but my suggestion would be to create new tables with the indexing set up correctly (Are you defining Primary Keys?), create the relationships, then populate the data from the old tables. You will benefit in the long run.

 

[SaraMegan]

Yes, I'm using primary keys and all that... When I first created the table, I indexed the field, but I had to change it for some reason (I can't remember what the reason was...)

thanks for your reply. I'll see what I can do to fix it.

--Sara