Create Table from other Tables

[Jaco]

Hello. I need to create a TABLE3 from TABLE1 and TABLE2.

TABLE1
field1
1000
2000

TABLE2
field2 field3
1 1
1 2
2 1
2 2

TABLE3
field1 field2 field3
1000 1 1
1000 1 2
1000 2 1
1000 2 2
2000 1 1
2000 1 2
2000 2 1
2000 2 2

Please help me to create it.

 

[John W. Vinson/MVP]

Hello. I need to create a TABLE3 from TABLE1 and TABLE2.

Why?

Serious question. This would have you apparently storing data
redundantly - the data that exists in Table3 would already exist in
the other two tables, wasting a LOT of space and leading to the risk
of update anomalies. If you changed the 1000 in Table1 to 1500, would
you expect all the records in TABLE3 to change? They wouldn't of
course!

TABLE1
field1
1000
2000

TABLE2
field2 field3
1 1
1 2
2 1
2 2

TABLE3
field1 field2 field3
1000 1 1
1000 1 2
1000 2 1
1000 2 2
2000 1 1
2000 1 2
2000 2 1
2000 2 2

Please help me to create it.

This looks like a "Cartesian Join Query". Add Table1 and Table2 to the
query grid, with NO join line; select all the fields from the two
tables. This will give you all possible combinations, as your example
suggests. If you have a good reason (which I find hard to imagine) for
doing so, change this query to a MakeTable query using the Query menu
item or the query-type tool on the toolbar. If not, base your report,
or form, or export directly on the query.

 

[Larry Linson]

Hello. I need to create a TABLE3 from TABLE1 and TABLE2.

TABLE1
field1
1000
2000

TABLE2
field2 field3
1 1
1 2
2 1
2 2

TABLE3
field1 field2 field3
1000 1 1
1000 1 2
1000 2 1
1000 2 2
2000 1 1
2000 1 2
2000 2 1
2000 2 2

In the Access Query Builder, add Table1 and Table2 as data sources (in the
box at the top), but don't try to join them -- you want to produce a
Cartesian Product. Now, drag each Field from both Queries to the Query Grid
below, Field1, Field2, and Field3. Run the Query.

If you have a compelling reason to replace Table1 and Table2 with Table3, go
back to design view, on the Menu, under Query, choose MakeTable, and when
prompted enter Table3. Execute the query and it will tell you that it is
about to write the records, to which you can reply OK or Cancel.

However, as John pointed out, if you are retaining Table1 and Table2,
there's no point to creating Table3 and storing the data redundantly -- just
use the Query described in the first paragraph whenever you'd use Table3.
And, not only does it store the same data again, it stores 8 records where
the first two tables only total 6 records -- if you have large tables, that
might be an argument against replacing Table1 and Table2 with a generated
Table3.

Larry Linson
Microsoft Office Access MVP