Question Enforcing relational integrity of compound key fields

[SaviorSix]

Hello - slightly basic question but have been unable to find what I'm looking for thru search. This is more of a 'theoretical' question

Table1:
Field1
Field2
Both fields comprise the compound PK

Table2:
Field1 (fk to [Table1] - I want this to be a concantenation of F1 and F2 from table 1)

I can create 2 individual relations between Field1, Field2 and [Table2].[Field1] in the Edit Relationships window:

Table/Query | Related Table/Query
Table1__________Table2
Field1------------Field2
Field2------------Field2

Each relation on its own is 'Indeterminate,' however once I add all four fields, the edit relationship window shows the relationship type turning from Indeterminate to One-to-many, so I know that Access realizes the relationship is there. However, I cannot enforce referential integrity.

I get an error where Access thinks the field types are different, or the field sizes are different, even though they are not (and I've tried several different field sizes for both text and number)

Is there some other way to enforce referential integrity in the example above?

 

[MStef]

Something is different, but, what is the primary key in the "Table2" ??

 

[SaviorSix]

Something is different, but, what is the primary key in the "Table2" ??

Most likely an autonumber key field

 

[MStef]

Put the simple relationship first, then right click on the relationship line.
Click on "Edit Relationship", form "Edit Relationships" appear on the
screen. Here you can Enforce Referential integrity, and click on Ok button.
Look at attachment, (word, zip).
It is for Access 2002-2003.

 

[SaviorSix]

Put the simple relationship first, then right click on the relationship line.
Click on "Edit Relationship", form "Edit Relationships" appear on the
screen. Here you can Enforce Referential integrity, and click on Ok button.
Look at attachment, (word, zip).
It is for Access 2002-2003.

Yes thank you - but that's my whole problem. Enforcing referential integrity in this case is not allowed for some reason.

Edit: Just looked at your example. In this case, I would want to relate both field1 and field2 to field21.

 

[MStef]

I don't know, I think it's impossible.

 

[Galaxiom]

Table2:
Field1 (fk to [Table1] - I want this to be a concantenation of F1 and F2 from table 1)

Perhaps you are using the word "concatenation" incorrectly.

Either way I cannot image any reason to do anything remotely like this. The requirement suggests you have normalization errors.