If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
autonumber relationship
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? |
#3
|
|||
|
|||
autonumber relationship
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? |
#4
|
|||
|
|||
autonumber relationship
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] |
#5
|
|||
|
|||
autonumber relationship
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. |
#6
|
|||
|
|||
autonumber relationship
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] |
Thread Tools | |
Display Modes | |
|
|