Reference a field by using the value of another

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hope somebody can help here.

I have a table and I need to reference a field using the
value of another.
For example I have 3 fields named "sp_1", "sp_2" and
"num_periods".

If the value in "num_periods" is "2" I want to retrive the
value in "sp_2".

So in psuedo sql:-

select * from sp_"num_periods"

any help would be appreciated.

Dave
 
Dav

Try the IIF Statement eg

SELECT Num_Periods , IIF(Num_Periods = 1, SP_1, SP_2
FROM Table

Regard
Rowan
 
-----Original Message-----
Dave

Try the IIF Statement eg:

SELECT Num_Periods , IIF(Num_Periods = 1, SP_1, SP_2)
FROM Table3

Regards
Rowan
.
Click to expand...

Thanks Rowan,

But it's not quite what I'm looking for.
I won't know what the value is of 'Num_Periods' but i will
know it is in the range of 1 to 50 and the fields sp_1 and
sp_2 will run up to sp_50.
But I need to use the value of 'Num_Periods' to reference
the correct 'sp_' field.
So if the value of 'Num_Periods' is '13' the I need to
reference the field 'sp_13'.

thanks,

Dave
 
-----Original Message-----
Dave

Try the IIF Statement eg:

SELECT Num_Periods , IIF(Num_Periods = 1, SP_1, SP_2)
FROM Table3

Regards
Rowan
.
Click to expand...

Thanks Rowan,

But it's not quite what I'm looking for.
I won't know what the value is of 'Num_Periods' but i will
know it is in the range of 1 to 50 and the fields sp_1 and
sp_2 will run up to sp_50.
But I need to use the value of 'Num_Periods' to reference
the correct 'sp_' field.
So if the value of 'Num_Periods' is '13' the I need to
reference the field 'sp_13'.

thanks,

Dave
 
Dear Dave:

Put the 50 columns in a separate table, with the columns of a unique
index of your existing tables, and a column for 1 to 50. Then you can
JOIN on this using the value of Num_Periods. This makes it very easy.

What you have now is an abomination. Sorry, but you're the one paying
the price for this, and the sooner you understand how it can be done
easily the better for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top