Value from Column based on Value in Field!

[HeyMoe]

Is Access SQL able to do this:

I want the value from a particular column, whose name is derived from the value in the field of another column?

Eg.
Col1 Col2 Col3 Col4 Col5
1 ABC 234 Col2 ABC
2 BCD 345 Col3 345
3 CDE 456 Col2 CDE
4 DEF 567 Col2 DEF
5 EFG 678 Col3 678
6 FGH 789 Col2 FGH

As you can see, I want the value in column 5 to be derived from the value in Column 4.
Is this possible to be done in SQL.
Thanks very much.

By the way, I'm using Access2007.

 

[billmeye]

Yes:

DoCmd.RunSQL "UPDATE tblYourTable SET tblYourTable.Col5=tblYourTable.Col4;"

 

[HeyMoe]

Thanks Bill for the quick reply.
Wouldn't that just set Col5 to what is in Col4, rather than the value in the Column that is referenced by Col4?
Thanks

 

[billmeye]

I just showed you for an example, you would have to fill in with your formula and any filter criteria. If you need more help let me know exactly what you are trying to achieve.

 

[HeyMoe]

Maybe this will help in my endeavour.
Please see attached file.

Thanks.

 

[Galaxiom]

Your task requires a dynamic query assembled in VBA. You could probably avoid the need for this complication by changing your data stucture.


Put all the data that is currently in the separate "columns" into the same field. Then add another field to indicate whatever it is that you have used to separate them into different fields.

This allows the table to be searched using an ordinary saved query with parameters.

 

[HeyMoe]

I came up with the solution of adding a very complicated IIF statement in to the SQL.
But, your solution sounds more elegant.
I would like to restructure the data in the table, but the table data isn't mine. I just had to come up with a front end.
Your suggestion of a query written in VBA seems to be a nicer solution.
Thanks very much.

Your task requires a dynamic query assembled in VBA. You could probably avoid the need for this complication by changing your data stucture.


Put all the data that is currently in the separate "columns" into the same field. Then add another field to indicate whatever it is that you have used to separate them into different fields.

This allows the table to be searched using an ordinary saved query with parameters.

 

[MSAccessRookie]

Yes:

DoCmd.RunSQL "UPDATE tblYourTable SET tblYourTable.Col5=tblYourTable.Col4;"

For the Record, I cannot see a reason to need the required design,

BUT:

It sounds like a simple adaption to billmeye's suggestion might do what you are looking for.

is this what you are trying to do?

UPDATE tblYourTable SET tblYourTable.Col5 = IIf(tblYourTable.Col4 = "Col2", tblYourTable.Col2, tblYourTable.Col3);

Based on what you have displayed, the above SQL Code should work, but if the content of the Data ever changes, then the SQL could stop working until it is also modified. Galaxiom's VBA Solution has the added value of flexibility and ease of support.

-- Rookie