Column Number as parameter in Query to select data

[Guest]

Hi is it possible that we can give Column Number as a parameter to select
Records at Runtime in access table .

eg, Table having structure like this, Where Qtr1..Qtr4 Stores Amt Paid by
customer,
If runtime i provide Column name to quaery it should return Cust with
specified quarter like

"Select Cust,<Column No> from tbl" , Where Column No is the parameter

Cust,Qtr1,Qtr2,Qtr3,Qtr4
X-10000-20000-30000-4000

I know if the data table is normalized like this

Cust,Qtr-Sales
X-Q1-1000
X-Q2-2000
X-Q3-3000
X-Q4-4000
it is quite easy to select , but my proble is , presently i am getting data
is in above format only


Thanx

 

[John Spencer]

You have two ways to solve this problem. One is to use a normalizing union
query to fix the data and then use the union query as the source for further
queries

SELECT Cust, Qtr1, "1" as Quarter
FROM YourTable
UNION ALL
SELECT Cust, Qtr2, "2" as Quarter
FROM YourTable
UNION ALL
SELECT Cust, Qtr3, "3" as Quarter
FROM YourTable
UNION ALL
SELECT Cust, Qtr4, "4" as Quarter
FROM YourTable

An alternative is to use
SELECT Cust, Switch([Which quarter?],Qtr1,Qtr2,Qtr3,Qtr4) as TheValue
FROM YourTable

Actually using nested IIF may be faster than the above

SELECT Cust
, IIF([Which Quarter?] = 1, Qtr1,
IIF([Which Quarter?] = 2, Qtr2,
IIF([Which Quarter?] = 3, Qtr3,
IIF([Which Quarter?] = 4, Qtr4,Null)))) as TheValue
FROM YourTable
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..