Missomissou
Member
- Local time
- Yesterday, 18:34
- 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?
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?
