Question

Locked

MS Access query help?

By corujo ·
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

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Well if yoare after something like this

by Tony Hopkinson In reply to MS Access query help?

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

Back to Web Development Forum
1 total post (Page 1 of 1)  

Related Forums