Solved A lookup table, attempted referential integrity, and the puzzle of the no unique index error (1 Viewer)

Missomissou

Member
Joined
Jan 30, 2024
Messages
51
Hi, before reading on, know that I have since deleted the lookup fields from all tables.

I have been overhauling the relationships between tables in my database (thanks to some very helpful advice from this forum). When initially creating the tables, I populated them with data directly in table view--and so used lookup fields to select from lists held in other tables. However in future, I hope that users within my organization will continue to populate the tables via associated forms that I've created. I created junction tables to connect the lookup tables to the primary tables, deleted the direct relationships, and changed all the lookup fields within tables to text boxes (with varying formats, of course). As it turns out, some of those direct relationships were probably correct in the first place, for example, in instances where I would like to choose, via a combo box in a form, from a list of items in a separate table. I am currently working with two primary tables, "O's projects" and "SciD Products", each of which has a field named "SciDProductType". I joined the list table to O's projects with a 1 to many relationship, and enforced referential integrity, with no issues. However, when I try to do the same with the SciD Products table, I receive an error message saying there is no unique index for the referenced field in the primary table. Incidentally, both tables have PKs, so I don't think that's the issue. Here's a screenshot of the relationships. Do you know what I'm doing wrong here?

1706884896349.png
 
I hope that users within my organization will continue to populate the tables via associated forms that I've created.
You need to lock down the app at least a little to guide them on the path to righteousness

We can't see enough of the diagram to see the problem. The boxes are too small to show the full names. Please list the actual table and column names that the dialog is trying to connect.
 
Are those really your table names? If so those are pretty bad choices.
"Primary Table-Olga's Projects" ???
Get rid of special characters and spaces. How about?
"ptblOlgasProjects"
 
You need to lock down the app at least a little to guide them on the path to righteousness

We can't see enough of the diagram to see the problem. The boxes are too small to show the full names. Please list the actual table and column names that the dialog is trying to connect.
Yes--good idea. I will tackle that after I get everything properly set up. Not sure righteousness is their path though...
 

Users who are viewing this thread

Top Bottom