Relationship Question

[Veli Izzet]

Hi,

I have two tables:

Tbl1:
PrID (Txt)- Primary key
Prtxt1
PrTxt2

Tbl2:
MlzID (Autonumber)-Primary key
Mlz1
Mlz2

I want to build a relationship between them by table 3 like

Tbl3:
PrID (Txt) primary key
MlzID(Number)primary key
Detailfield

Question:
If I want to check for referential integrity Access does not allow me
because Mlz ID is Autonumber in tbl2, and number in tbl3. If I change
the MlzID to autonumber in tbl3, I cannot succeed.

If I do not check for referential integrity, the relationship can be
established.

Can anybody explain this behaviour.

Thanks for answers.

 

[Allen Browne]

You should be able to create the relation with referential integrity if you
meet these 3 conditions with your tables:

a) Tbl2 is the primary table in the relationship when you try to create the
relation. (It will be on the left in the Create Relation dialog.)

b) The Number sizes match. For example if Tbl2.MIzID is a AutoNumber of size
Long Integer, then Tbl3.MIzID must be a Number if size Long Integer.

c) You do not already have bad data in the tables (i.e. a record in Tbl3,
where MIzID does not match any MIzID value in Tbl2.