Web Development

Ask something
Question Locked

MS Access query help?

0 helpful
2 replies

by corujo

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello all,
Trying to create a query to rank some mines by production. The PRODUCTION table has MetalID columns thru MetalID_5(nulls allowed), but the Metal_Name is in the METAL table. Each row has only one instance of any given MetalID, but the ID can be in any of the MetalID_x columns depending on the primary metal for the mine. For example, a copper mine(MetalID1 = 205) may have gold as a byproduct(MetalID2 = 320), but another mine may have gold as the primary and copper as byproduct. I would like to have a MetalNamex(Metal1, Metal2, etc.) column with values that correspond to each MetalID column, so that a reader will not have to look up what the metals are.
Thanks

2 replies

by tony hopkinson

Gold Copper Tin Total
MineA 10 20 30
MineB 40 80 120
Total 50 20 80 150

Then first you want to mormalise the silly design with

Select Mine,MetalName,Tonnes
From Metals,Production
Where Metals.MetalID = Production.MetalID_1
Union
Select Mine,MetalName,Tonnes
From Metals,Production
Where Metals.MetalID = Production.MetalID_2
....
Union
Select Mine,MetalName,Tonnes
From Metals,Production
Where Metals.MetalID = Production.MetalID_5

Then you need to look up how access does crosstab, but that function should produce the top table (ish) in one go from the normalised data.

HtHs

This discussion is closed to new replies.