Need to match or join a field to a column name

[lisaharris]

I have a table with computed values that have column names such as Volume1, Volume2, Volume3.
Second table with the plan name, and a field indicating which volume to use (so the row may be PLAN1, Volume2)
The indicator in the second table is the exact name of the column in the first table.

How do I select the value from the first table corresponding to the indicator in the second table? Basically I want to see PLAN1 and the actual number from Volume2 in the first table.

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[SkipVought]

lisa,

Your table is not normalized: hence your problem! Your problem is a result of the improper structure of this table. Furthermore, what happens when you get a Volume4?

Short of correcting your design structure, you'll need to perform UNION querys for each volumn heading and HARD CODE the Heading value in the SQL...


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lisaharris]

The tables are normalized......
Table 1 looks like this:
Rep Plan Volume1 Volume2 Volume3
100 A1 50000 60000 50000
200 A2 45000 45000 55000

Table 2 looks like this:
Plan VolumeIndicator
A1 Volume1
A2 Volume3

There will never be a Volume4. I calculated the volume columns based on different needs.
What I want to do is select the rep from table 1, match the plan to table 2, find which volume column to use and select that number from table 1.




__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[SkipVought]

The tables are normalized

HUH???!!!

THIS id NORMALIZED
[pre]
Rep Plan Vol VolVal
100 A1 Volume1 50000
100 A1 Volume2 60000
100 A1 Volume3 50000
200 A2 Volume1 45000
200 A2 Volume2 45000
200 A2 Volume3 55000
[/pre]

Now using THAT, you can accomplish your task.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lisaharris]

Back to the drawing board. Thanks anyway.

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[MajP]

make a new query

Select Plan, "Volume1" as VolumeIndicator, Volume1
from TestOne
UNION
Select Plan, "Volume2" as VolumenIndicator, Volume2
From TestOne
UNION
Select Plan, "Volume3" as VolumenIndicator, Volume3
From TestOne

now you can link the table to this query by your volume indicator

 

[lisaharris]

MajP,
Little massaging and redesign of my master query and presto -- Works like a charm! Union query did exactly what I needed to accomplish.

Thanks so much!

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[dhookom]

I agree with MajP and SkipVought that your table structure isn't normalized. Since you seem to have implemented the union query, you might get a little better performance by replacing "UNION" with "UNION ALL".

Duane
Hook'D on Access
MS Access MVP