Combine tables query

S

Sammy

How do I combine table 1 and table 2 to create table 3 in
a query? I can't figure out how to relate table 1 &2.
Weight field is numerical, zone field is alpha and rate
field is currency. Thanks.

Table 1
Zone
Weight A B C
1 $38.76 $40.40 $39.30
2 $42.52 $45.59 $44.06
3 $48.38 $50.78 $52.80


Table 2
Weight
Zone 1 2 3
A $38.76 $42.52 $48.38
B $40.40 $45.59 $50.78
C $39.30 $44.06 $52.80

Table 3
Zone Weight Rate
A 1 $38.76
A 2 $42.52
A 3 $48.38
B 1 $40.40
B 2 $45.59
B 3 $50.78
C 1 $39.30
C 2 $44.06
C 3 $52.80
 
D

Douglas J. Steele

Um, what's the difference between tables 1 and 2? Both contain the exact
same information (and both are denormalized versions of table 3, which is
really how your data should be stored)

Either table can be converted to the proper form using UNION queries.

To convert Table 1 to Table 3, use:

SELECT "A" As Zone, Weight, A As Rate FROM Zone
UNION
SELECT "B" As Zone, Weight, B As Rate FROM Zone
UNION
SELECT "C" As Zone, Weight, C As Rate FROM Zone

To convert Table 2 to Table 3, use

SELECT Zone, 1 As Weight, [1] As Rate FROM Weight
UNION
SELECT Zone, 2 As Weight, [2] As Rate FROM Weight
UNION
SELECT Zone, 3 As Weight, [3] As Rate FROM Weight

Anytime your field names represent actual data, you should seriously
reconsider your table design.
 
S

Sammy

The problem is that my data came to me as table 1, but I
want it to look like table 3. Table 2 was just a
transposed version of table 1 trying to get it to look
like table 3. So ignoring table 2, I think I can create
table 3 now. Thanks for your help.
-----Original Message-----
Um, what's the difference between tables 1 and 2? Both contain the exact
same information (and both are denormalized versions of table 3, which is
really how your data should be stored)

Either table can be converted to the proper form using UNION queries.

To convert Table 1 to Table 3, use:

SELECT "A" As Zone, Weight, A As Rate FROM Zone
UNION
SELECT "B" As Zone, Weight, B As Rate FROM Zone
UNION
SELECT "C" As Zone, Weight, C As Rate FROM Zone

To convert Table 2 to Table 3, use

SELECT Zone, 1 As Weight, [1] As Rate FROM Weight
UNION
SELECT Zone, 2 As Weight, [2] As Rate FROM Weight
UNION
SELECT Zone, 3 As Weight, [3] As Rate FROM Weight

Anytime your field names represent actual data, you should seriously
reconsider your table design.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


How do I combine table 1 and table 2 to create table 3 in
a query? I can't figure out how to relate table 1 &2.
Weight field is numerical, zone field is alpha and rate
field is currency. Thanks.

Table 1
Zone
Weight A B C
1 $38.76 $40.40 $39.30
2 $42.52 $45.59 $44.06
3 $48.38 $50.78 $52.80


Table 2
Weight
Zone 1 2 3
A $38.76 $42.52 $48.38
B $40.40 $45.59 $50.78
C $39.30 $44.06 $52.80

Table 3
Zone Weight Rate
A 1 $38.76
A 2 $42.52
A 3 $48.38
B 1 $40.40
B 2 $45.59
B 3 $50.78
C 1 $39.30
C 2 $44.06
C 3 $52.80
Click to expand...


.
Click to expand...
 
S

Sammy

Thanks Doug - I've got my union query. What's the best
way to turn my union query into a table?
-----Original Message-----
Um, what's the difference between tables 1 and 2? Both contain the exact
same information (and both are denormalized versions of table 3, which is
really how your data should be stored)

Either table can be converted to the proper form using UNION queries.

To convert Table 1 to Table 3, use:

SELECT "A" As Zone, Weight, A As Rate FROM Zone
UNION
SELECT "B" As Zone, Weight, B As Rate FROM Zone
UNION
SELECT "C" As Zone, Weight, C As Rate FROM Zone

To convert Table 2 to Table 3, use

SELECT Zone, 1 As Weight, [1] As Rate FROM Weight
UNION
SELECT Zone, 2 As Weight, [2] As Rate FROM Weight
UNION
SELECT Zone, 3 As Weight, [3] As Rate FROM Weight

Anytime your field names represent actual data, you should seriously
reconsider your table design.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


How do I combine table 1 and table 2 to create table 3 in
a query? I can't figure out how to relate table 1 &2.
Weight field is numerical, zone field is alpha and rate
field is currency. Thanks.

Table 1
Zone
Weight A B C
1 $38.76 $40.40 $39.30
2 $42.52 $45.59 $44.06
3 $48.38 $50.78 $52.80


Table 2
Weight
Zone 1 2 3
A $38.76 $42.52 $48.38
B $40.40 $45.59 $50.78
C $39.30 $44.06 $52.80

Table 3
Zone Weight Rate
A 1 $38.76
A 2 $42.52
A 3 $48.38
B 1 $40.40
B 2 $45.59
B 3 $50.78
C 1 $39.30
C 2 $44.06
C 3 $52.80
Click to expand...


.
Click to expand...
 
D

Douglas J. Steele

Save the query.

Create a second query based on the first query, and turn the second query
into a MakeTable query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sammy said:
Thanks Doug - I've got my union query. What's the best
way to turn my union query into a table?
-----Original Message-----
Um, what's the difference between tables 1 and 2? Both contain the exact
same information (and both are denormalized versions of table 3, which is
really how your data should be stored)

Either table can be converted to the proper form using UNION queries.

To convert Table 1 to Table 3, use:

SELECT "A" As Zone, Weight, A As Rate FROM Zone
UNION
SELECT "B" As Zone, Weight, B As Rate FROM Zone
UNION
SELECT "C" As Zone, Weight, C As Rate FROM Zone

To convert Table 2 to Table 3, use

SELECT Zone, 1 As Weight, [1] As Rate FROM Weight
UNION
SELECT Zone, 2 As Weight, [2] As Rate FROM Weight
UNION
SELECT Zone, 3 As Weight, [3] As Rate FROM Weight

Anytime your field names represent actual data, you should seriously
reconsider your table design.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


How do I combine table 1 and table 2 to create table 3 in
a query? I can't figure out how to relate table 1 &2.
Weight field is numerical, zone field is alpha and rate
field is currency. Thanks.

Table 1
Zone
Weight A B C
1 $38.76 $40.40 $39.30
2 $42.52 $45.59 $44.06
3 $48.38 $50.78 $52.80


Table 2
Weight
Zone 1 2 3
A $38.76 $42.52 $48.38
B $40.40 $45.59 $50.78
C $39.30 $44.06 $52.80

Table 3
Zone Weight Rate
A 1 $38.76
A 2 $42.52
A 3 $48.38
B 1 $40.40
B 2 $45.59
B 3 $50.78
C 1 $39.30
C 2 $44.06
C 3 $52.80
Click to expand...


.
Click to expand...
Click to expand...
 
S

Sammy

Thanks! Works!
-----Original Message-----
Save the query.

Create a second query based on the first query, and turn the second query
into a MakeTable query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug - I've got my union query. What's the best
way to turn my union query into a table?
-----Original Message-----
Um, what's the difference between tables 1 and 2? Both contain the exact
same information (and both are denormalized versions
Click to expand...
of
table 3, which is
really how your data should be stored)

Either table can be converted to the proper form using UNION queries.

To convert Table 1 to Table 3, use:

SELECT "A" As Zone, Weight, A As Rate FROM Zone
UNION
SELECT "B" As Zone, Weight, B As Rate FROM Zone
UNION
SELECT "C" As Zone, Weight, C As Rate FROM Zone

To convert Table 2 to Table 3, use

SELECT Zone, 1 As Weight, [1] As Rate FROM Weight
UNION
SELECT Zone, 2 As Weight, [2] As Rate FROM Weight
UNION
SELECT Zone, 3 As Weight, [3] As Rate FROM Weight

Anytime your field names represent actual data, you should seriously
reconsider your table design.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


How do I combine table 1 and table 2 to create
Click to expand...
table 3
in
a query? I can't figure out how to relate table 1 &2.
Weight field is numerical, zone field is alpha and rate
field is currency. Thanks.

Table 1
Zone
Weight A B C
1 $38.76 $40.40 $39.30
2 $42.52 $45.59 $44.06
3 $48.38 $50.78 $52.80


Table 2
Weight
Zone 1 2 3
A $38.76 $42.52 $48.38
B $40.40 $45.59 $50.78
C $39.30 $44.06 $52.80

Table 3
Zone Weight Rate
A 1 $38.76
A 2 $42.52
A 3 $48.38
B 1 $40.40
B 2 $45.59
B 3 $50.78
C 1 $39.30
C 2 $44.06
C 3 $52.80



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rate Look-up 1
VLOOKUP, a table with text and numerics 7
How do I combine 2 tables? 3
linked table using SQL Query 8
Combine 2 tables 1
Query combining information 3 tables 2
Query Question 2
needed SQL queries 1

Top