Access Relationships

[Pendelfin]

I will try to explain this as simply as I can.

I have table A and table B which both have a 1 to many relationship to Table
C - this works fine.

Example Table A has its unique reference which only occurs once here but
many times in table C, the same with Table B's Reference showing many times
in Table C. However I am now trying to link Table C to Table D whereas a
reference in table C is unique but will appear many times in Table D -
however my relationship is showing as Indeterminate?

Hopefully this will be a little clearer

Table A Table B

Ref Ref
AA11 ZZ99
BB22 YY88
CC33 XX77
DD44 WW66

Table C

Ref Ref A Ref B
AA11-ZZ99 AA11 ZZ99
AA11-XX77 AA11 XX77
BB22-XX77 BB22 XX77

Table D

Ref Ref C
1 AA11-XX77
2 AA11-XX77
3 AA11-ZZ99

I hope this is clear and many thanks for your help

 

[Pendelfin]

I will try to explain this as simply as I can.

I have table A and table B which both have a 1 to many relationship to Table
C - this works fine.

Example Table A has its unique reference which only occurs once here but
many times in table C, the same with Table B's Reference showing many times
in Table C. However I am now trying to link Table C to Table D whereas a
reference in table C is unique but will appear many times in Table D -
however my relationship is showing as Indeterminate?

Hopefully this will be a little clearer

Table A Table B

Ref Ref
AA11 ZZ99
BB22 YY88
CC33 XX77
DD44 WW66

Table C

Ref Ref A Ref B
AA11-ZZ99 AA11 ZZ99
AA11-XX77 AA11 XX77
BB22-XX77 BB22 XX77

Table D

Ref Ref C
1 AA11-XX77
2 AA11-XX77
3 AA11-ZZ99

Where am I going wrong and why is this not showing as a 1 to many
relationship?

 

[Ken Sheridan]

C models a many-to-many relationship between A and B by resolving this into
two one-to-many relationships. Consequently the Ref A and Ref B columns of C
are a candidate key and can be made the (composite) primary key of C. D will
therefore have a composite foreign key of columns Ref A and Ref B, with the
one-to-many relationship from C to D being on both columns.

You therefore need to delete the redundant Ref columns from C and D and add
two columns, Ref A and Ref B to the latter. You will then be able to create
and enforce the relationship.

Alternatively you could give C a surrogate autonumber primary key, and D a
corresponding long integer foreign key column, creating the relationship on
these. If you do this, however, its imperative that you also create a unique
index on C's Ref A and Ref B columns, i.e. a single index on both columns,
not separate indexes on each.

What you must not do is have a primary key of C whose values are derived
from the values of its Ref A and Ref B columns, as your dummy data shows.
This introduces redundancy and leaves the door wide open to inconsistent data.

Ken Sheridan
Stafford, England