Reference table column computationally in sql query - how

[PeterDuthie]

Hello All. I have a stacked query which returns a series of rows of numbers (1-5) which refers to one of 5 columns in a table. How can I (or maybe I can't do this) code the column names with the numbers returned from the stacked query. Here's an example of what I want to do.

I want to end up with a query such as
SELECT MyColumn1 (or MyColumn2, or MyColumn3, or MyColumn4, or MyColumn5)
FROM tblTestColumns;

tblTestColumns has columns named MyColumn1 ... MyColumn5

So I want to do something like
SELECT MyColumn & str(NumberFromStackedQuery)
FROM tblTestColumns;

Any help greatly appreciated as always. Maybe, I'm tacking this the wrong way?

Cheers,
Peter

 

[Cagliostro]

As far as I understand you want:
-------
SELECT MyColumn1
FROM tblTestColumns
where thing1
union
SELECT MyColumn2
FROM tblTestColumns
where thing2
union
SELECT MyColumn3
FROM tblTestColumns
where thing3
union
SELECT MyColumn4
FROM tblTestColumns
where thing4
union
SELECT MyColumn5
FROM tblTestColumns
where thing5
----------
thing means some conditions

John Fill

ivfmd@mail.md

 

[PeterDuthie]

Thanks a lot for your help John. I hadn't tried the Union approach because column1 ... column5 are in the same table. I have tried now tried a union but it doesn't work - not the way I've coded it anyway.

Cheers,
Peter

 

[PeterDuthie]

Hello John and anyone else who reads this. John, you were right about the UNION. It took a while for the penny to drop but what I've done is split up the rows to return 5 recordsets with each discrete Column(X) along with the rest of the data ... then UNION(ed) them. So now I can do my grouping in any way I want for the reports now - great))))

Cheers, and thanks again,
Peter