autonumber relationship

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?

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


--
Dave Hargis, Microsoft Access MVP


"CuriousMark" wrote:

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?

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.

"Klatuu" wrote:

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


--
Dave Hargis, Microsoft Access MVP


"CuriousMark" wrote:

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?

On Tue, 23 Jun 2009 09:43:01 -0700, CuriousMark
wrote:

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)?
--

John W. Vinson [MVP]

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.

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

"John W. Vinson" wrote:

On Tue, 23 Jun 2009 09:43:01 -0700, CuriousMark
wrote:

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)?
--

John W. Vinson [MVP]