Junction Table

[chris11590]

i have created a junction table which include IDs from two other tables, which i have made both the primary keys. (this is how i understand junction tables are created?). However, i just read a post, a user had one PKs (the junction table) and two FKs (IDs from the other table). I am trying to accomplish a many to many relationship.

The problem is I can not set the datatype to autonumber because i get the message you can not use two autonumber twice; therefore i have set them to text. however, later when i try to join the tables i can not because of different datatypes.

bald! Chrissy

 

[MSAccessRookie]

The standard Junction table has three columns:

1. An AutoNumber Field for the Junction Table to use as its Primary Key
2. A Number Field as a Foreign Key representing The Primary Key in Table #1
3. A Number Field as a Foreign Key representing The Primary Key in Table #2

The Foreign Keys are not intended to be AutoNumber, since they can each be duplicated as needed.

 

[chris11590]

So if a number field is used as a foreign key representing the primary key in table one, when you view the datasheet you will see the record in numbers vs text. can you see text instead?

 

[MSAccessRookie]

So if a number field is used as a foreign key representing the primary key in table one, when you view the datasheet you will see the record in numbers vs text. can you see text instead?

Please describe your tables if the following example is not of assistance.

The structure of an average table might be something like this:

PrimaryKey: AutoNumber 
OtherColumn1: <choose a datatype> 
OtherColumn2: <choose a datatype> 
OtherColumn3: <choose a datatype>

The Structure of the Junction Table might be like this:

PrimaryKey: AutoNumber 
Table1FK: Number < FK from Table1 >
Table2FK: Number < FK from Table2 >

If you have two tables (Table1 and Table2) and a junction table that look like the above examples, a query that looks like the following would get the other values for you:

Select Table1.OtherColumn1, Table1.OtherColumn2, Table1.OtherColumn3, Table2.OtherColumn1, Table2.OtherColumn2, Table2.OtherColumn3
From ((TblJunction INNER JOIN Table1 ON tblJucntion.Table1FK = Table1.PrimaryKey) INNER JOIN Table2 ON tblJucntion.Table2FK = Table2.PrimaryKey))