How to create Many-to-Many "Linking" or "Relational" table ?

[Will]

In reworking an old flat file data base I have ended up with 2 tables that
need to be connected or linked together... but to do so would create a many
to many relationship.

So, I need to create a "linking" table... like the "items" table that sits
between the Orders and Products table.

My question is how do I create that linking table and get the proper id's in
it that will link each record properly?

using the Odes & Products example my linking table will have two fields for
each record...

idOrder and idProduct

the idOrder will link to the matching number in the Orders Table
the idProduct will link to the matching number in the Products Table

thanks for any tips on an easy way to do this.

 

[Steve Schapel]

Will,

It seems to me that you have already answered your own question. Yes,
the table will include a foreign key field to relate to the primary key
field in each of the other tables, exactly as you have described.
Typically, this table will also contain other fields as well, that will
pertain to each Item record, for example QuantityOrdered, or some such.

If you need more explicit help with this, it may be a good idea to share
the details of your actual project.

In the meantime, you may find this article of interest...
http://accesstips.datamanagementsolutions.biz/many.htm

 

[Will]

Steve,

- We know we ned to create the linking table.
- And we know that it will have two fields...- the ID of Table A and the ID
of Table B
- But... we already have Table A and Table B with a bunch of data in each.
- So, mechanically, how do we create the linking table and get the proper
ID's into each record?

Here is what we have...

Table-A
idA
Afield-1
Afield-2
Afield-3

Table-B
idB
Bfield-1
Bfield-2
Bfield-3

Here is what we need to create...

Table-Linking
idA
idB

So, is there a trick or an easy way to create and populate Table-Linking so
that the proper id numbers are in each record to match the proper record in
Table-A with the proper record in Table-B?

That is the question.

 

[Guest]

No trick, just a lot of thinking and plinking.

Unless you have a rationale connection between some data in each table.

 

[John Vinson]

So, is there a trick or an easy way to create and populate Table-Linking so
that the proper id numbers are in each record to match the proper record in
Table-A with the proper record in Table-B?

That is the question.

Given the information that you have provided, no, there is not.

If there is no information in TableA which indicates that a particular
record is related to a record in TableB, and vice versa, then there is
no way - even in principle - to populate the junction table.

Let's say you had two file folders, one containing sheets of paper
about people and their contact information; the other containing
sheets of paper about vacation trip packages.

Which people went on which trips?

Do you have some OTHER information - perhaps an original wide-flat
table with duplicate information - that might provide this link?

John W. Vinson[MVP]

 

[Dave Emmert]

You can create a pass-through table:

Table-C
idA
idB

Set the relationships for (one-to-many) from Table-A to Table-C with idA and
the same for Table-B.

Dave

 

[TC]

Say you had a flat table like this:

Order Product other guff
111 222 ...
111 333 ...
555 222 ...
555 444 ...

That table is actually the linking table. You'd just create the Orders
table like this:
CREATE TABLE tblOrders AS SELECT DISTINCT Order FROM FlatTable;
and the Products table in a similar fashion.

If the flat table had other fields pertaining to the order number
regardless of product - eg. the order date - you'd just include those
fields in the SELECT DISTINCT list. That could lead to duplicate order
numbers in the new Orders table, if, for example, the same order number
appeared with different order dates, in the flat table. You'd simply
let that occur, then clean up those duplcates, then set order number as
the primary key of the new table and delete those fields from the flkat
(joining) table.

Similarly, if the flat table had other fields pertaining to the prouct
regardless of order number - eg. prouct name - you'd just include those
fields in the SELECT DISTINCT list, fix up any duplicates in the new
Products table, then set the PK of the new table.

Is that the kind of info. you're after?

HTH,
TC