Relationship table (1 Viewer)

[twixx]

Hello Access experts!

I have two tables. The master table uses the combination of Test Number and Original? as its primary key and the other table uses Test Number, Original?, and Position as its primary key.

I want to establish a one-to-many relationship by connection the Test Number and Original? together but I get the message: "No unique index found for the referenced field of the primary table." I've already tried connecting only the Test Number but it still doesn't seem to work. Does anyone have any ideas?

Thanks!

 

[akt01]

can u send a screen-shot of relationships screen

 

[akt01]

can u post a screen-shot of your Relationships screen

 

[SOS]

Does anyone have any ideas?

Yes, I have a suggestion -

Get rid of the composite keys and use an autonumber as the primary key for both. Then set a multi-field index on the fields you don't want to have duplicates for. That way it actually simplifies things as you only have to worry about inserting one value in your child table as a Long Integer which would be the Autonumber from the main table.

Composite keys in my view are a pain in the @$$ to maintain and use.

 

[The_Doc_Man]

1. Concur with SOS about using compound primary keys except in very rare cases.

2. If you really have a field name with a question mark in it, get rid of the punctuation. Some day you'll need to do something that involves syntax that can't tolerate the ? symbol. If that was just a short-hand you used for this question only, then OK.

3. In the second table, if you use SOS's suggestion, you can eliminate two fields and replace them with one.

Tbl1: T1ID (autonumber, PK); Test Number, Original

Tbl2: T2ID (autonumber, PK); T1ID, Position.

(You need T2ID if a third table would depend on this record.) Since you can look up test number and original from Tbl1 having T1ID from Tbl2, you don't need to store them a second time.

4. If Original? represented a yes/no field, there is another reason you don't want that as a PK. Y/N fields make LOUSY index contributors. They aren't worth the trouble. As SOS points out, you can make a separate non-primary index to prevent duplication of the testnumber/original combinations.

5. Having the T1ID linkage available through Tbl2, you can establish a relationship between the two tables.