Primary Keys and Relationships

[TR]

I cannot form relationships with my tables. I keep getting indeterminate realtionships. I believe this is becasue I cannot define a primary key. I want to use three fields to define my primary key; however, I continue to get an error message that there are duplicates or the indexes are improperly marked. I indexed the fields to indicate yes or no for duplicates, but I still come up with an error. Any words of advice?

 

[rpw]

Usually you combine fields into a composite key when you know that the combination will not ever produce duplicates. The fact that the error message is saying that there are duplicates indicates that the combination is not producing a unique key

The other problem you are having with 'indeterminate relationships' is possibly caused by how you are relating one table to another

For example, if you have one table that has an Autonumber Primary Key (which is long-integer) and you want to link it to another table, the second table must have a field that is of the long-integer type and possess the same field length. The fields do not have to be of the same name, but it sure helps keeping things clear. (I suspect that when you are using a single field PK you do not have any of the problems you described.

If you have three fields in tblOne that make up the composite PK, then tblTwo must have (in addition to its PK) three fields of the same type that match the composite key. In the relationships window, you select all three fields and drag them into tblTwo and then define which fields relate to which fields

Of course, the easy fix for the first problem of having duplicates is to add an Auto#PK and relate the tables that way

Hope this helps

rp


----- TR wrote: ----

I cannot form relationships with my tables. I keep getting indeterminate realtionships. I believe this is becasue I cannot define a primary key. I want to use three fields to define my primary key; however, I continue to get an error message that there are duplicates or the indexes are improperly marked. I indexed the fields to indicate yes or no for duplicates, but I still come up with an error. Any words of advice?

 

[John Vinson]

I cannot form relationships with my tables. I keep getting indeterminate realtionships. I believe this is becasue I cannot define a primary key. I want to use three fields to define my primary key; however, I continue to get an error message that there are duplicates or the indexes are improperly marked. I indexed the fields to indicate yes or no for duplicates, but I still come up with an error. Any words of advice?

How are you trying to set the Primary Key? In table design view you
can ctrl-click each of the three fields, so they are all selected, and
then click the "key" icon; unless there are existing records for which
this set of fields has duplicates, you'll get a three-field primary
key which can then be used for your relationships. You need to draw
the relationship line THREE TIMES, one for each pair of matching
fields. Each field *individually* can have duplicates and should be
indexed as such, if you choose to create a separate index on the
individual field (it may not be necessary to do so).

If the program says there are duplicates ... there are probably
duplicates, and you'll need to fix them first. Run the "Find
Duplicates Query" wizard on the three fields to identify which records
are causing the problem.