Linking tables

  • Thread starter Thread starter Tor Inge Rislaa
  • Start date Start date
T

Tor Inge Rislaa

I have 3 tables, where Table1 should link in Table2 and Table3. I need an
example on how to do this.



Table1

Table1_ID, Table2_ID, Table3_ID (Table1_ID is an autonumber)



Table2

Table2_ID, Description



Table3

Table3_ID, Description



I want to insert records into Table1 and I need a query that can feed the
data according to:

For each ID in Table2 add every ID in Table3



If both Table2 and Table3 contained 3 ID's the result should look like:

Table1_ID, Table2_ID, Table3_ID

1 1 1

2 1 2

3 1 3

4 2 1

5 2 2

6 2 3

7 3 1

8 3 2

9 3 3



Any ideas
 
I want to insert records into Table1 and I need a query that can feed the
data according to:

For each ID in Table2 add every ID in Table3
Click to expand...

Ummmm...

Why?

Normally a many to many "resolver" table like this is used to say that
*this* record in Table2 is related to *that* record in Table3, or to
*these three* specific records, or to...

You're saying that every single record in Table2 is related to every
single record in Table3. If so, you don't really need Table1 to exist
at all, since it doesn't contain any new information!

That said... you can do this by creating a "Cartesian join" query.
Create a Query adding Table2 and Table3 to a Query window; don't
specify any join line. Select each table's ID and specify Ascending on
the Order By.

Change this query to an Append query and append the two fields to
Table1's Table2ID and Table3ID field. Let Table1's Autonumber take
care of its ID. If Table2 has 100 records, and Table3 has 200, you'll
end up with 20000 records in Table1, covering all possible
combinations.
 
Back
Top