autonumber relationship

[CuriousMark]

Trying to set up many-to-many relationship with an intersection table, but
having trouble with enforce referential integrity. Each table on the "one"
side has a primary key field with autonumber data type. The corresponding
field in the intersection table is a number data type. So when I try to
enforce referential integrity I get the error "Relationship must be on the
same number of fields with the same data types". I thought this is the way
many-to-many relationships are supposed to be created. What am I missing?

 

[Klatuu]

Think of each of the sides as being independant.

tblOneSide
OneSideID PK - Autonumber
<other fields>

tblOtherSide
OtherSideID PK - Autonumber

tblJunction
JunctionID PK - Autonumber
OneSideID Foreign Key - Long Integer
OtherSideID Foreign Key - Long Integer

 

[CuriousMark]

I understand that. But the problem is that when I create a relationship
between OneSideID in tblOneSide and OneSideID in tblJunction, I get an error
when I check the enforce referential integrity option because the field is
Autonumber in one table and Number in the other.

 

[John W. Vinson]

I understand that. But the problem is that when I create a relationship
between OneSideID in tblOneSide and OneSideID in tblJunction, I get an error
when I check the enforce referential integrity option because the field is
Autonumber in one table and Number in the other.

Is the Autonumber the default Long Integer type, or is it a GUID?
Is the foreign key field a Number of Long Integer size (or some other size,
which would cause this problem)?

 

[Fred]

An autonumber field is a long integer type.

So make your FK also a long integer type. In table design, after you pick
"number" (as you've already done) look at the dialog box at the bottom and
pick the number type.

 

[CuriousMark]

Thanks.....fk was Integer instead of Long Integer....