How to use one field to choose another field

[WFKASKIE]

This seems so simple yet I can't get anything to work.

I want the value of one field to determine from which column to get a bit of data.

I have a table with column names 1, 2, and 3.
I have a field called type that contains values 1, 2 and 3.

If Type has the value one in it, I want a field called "Value" to have the data from the column called "1". If the "Type" field has a value of 2, I want "Value" to have the data from the column called "2", etc.

Any ideas?

I've tried things like
Value: [[Type]]
and
Value: [ & [Type] & ]

 

[Dan Rittgers]

I have not used Access in a while.

It sounds like you want to join one column in one table to another column in another table. If they match, transfer the data (DATA) from one table to another (TARGET).

Create a column named DATA in the TARGET. Join those columns with the criteria????

 

[WFKASKIE]

Thanks Dan for your reply.

What I'm actually trying to do is put the data from one field into another. Essentially, just doing an alias. Normally, you can do something like this:

SELECT table1.a, table1.b, table1.c, table1.d, table1.a AS Value FROM table1

This is almost what I'm trying to accomplish. The twist is that I need "Value" to be either table.a or table1.b or table1.c depending on what's in table1.d. So, if the data in table1.d = "b" then:

SELECT table1.a, table1.b, table1.c, table1.d, table1.b AS Value FROM table1.

This would be the case for each record in table1.

A sample output would look something like this:

a , b , c , d , Value
Bob, Smith, Carpenter, "a", Bob
John, Adams, Carpenter, "a", John
Mary, Watts, Plumber , "b", Watts

So you see that the 5th column shows either the 1st, 2nd or 3rd column depending on what's in the 4th column.