Combine tables query

  • Thread starter Thread starter Sammy
  • Start date Start date
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
 
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.
 
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...
 
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...
 
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...
 
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...
 
Back
Top