I can't establish a relationship between tables with a look-up field. (1 Viewer)

[Jeanette]

Please help!!! I have a projects table that has a look-up field from a contacts table. When I try to enforce referential integrity, I get an error message saying no unique index found. I don't understand whats wrong. Also, when I try to use this look-up field in a query, my query returns no matches. Thanks in advance.

 

[mdemarte]

Does your contacts table have a unique index? If you don't have a field that you can use, create a key field and make it an auto-number type.

 

[Jeanette]

My contacts table does have a primary key that is set as auto-number type!!!

 

[mdemarte]

Does your projects table have a unique index? What field ties the two tables together and what kind of field is it?

 

[Jeanette]

My projects table has a unique index that is the primary key. I have a field in the projects table that looks up values in the contacts table that is connected by Cntct#( the primary key in the contacts table). I hope this is not to confusing. Here is a copy of SQL View of the look-up query:

SELECT [Bank Contacts].[Cntct#], Left([Bank Contacts]![CntctFst],1) & Left([Bank Contacts]![CntctLst],1) AS Expr1, [Bank Contacts].CntctLst, [Bank Contacts].CntctFst
FROM Bank INNER JOIN (Address INNER JOIN [Bank Contacts] ON Address.[Address#] = [Bank Contacts].[Address#]) ON Bank.TAXID = [Bank Contacts].TAXID
ORDER BY [Bank Contacts].CntctLst;

 

[mdemarte]

I just tried to connect two tables without primary keys, and got your error message, the relationship was indeterminate. After I changed a field to be the primary key in the parent database, the relationship now said one-to-many and I could try to enforce referential integrity.

To sum up: Your Contacts table is the child table, tied to the Projects table which is the parent table. This is a one-to-many relationship. The primary key for the Contacts table is Cntct#. Then, in the projects table, I would store the Cntct#. And in Tools, Relationships they can be connected.

If you have this set already, then I don't know why you are getting the error message. If this is not your join, then post back and hopefully someone will help you.

 

[Jeanette]

mdemarte, I wanted to thank you for your help. I did not have the cntct # as the primary key as I had thought. By making it the primary key my problem was solved. Thanks.