• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1994
  • Last Modified:

Indirect field reference in SQL statement: Possible??

WITHOUT using VBA and only using Access 2000, can I create a two-table query where the contents of a one field in one table can become the column name or field name in another.

TableOne has the following fields:
Field1 Text   10
Field2 Text   10
Field3 Text   10

TableTwo has one record and one field:
FieldName Text  10    <-  Holds the value "Field2"

I want to select only Field2 from table one.  I've tried the Eval() function without success:

select eval([TableTwo]![FieldName]) from TableOne

Thanks - gene
0
genehead
Asked:
genehead
1 Solution
 
nico5038Commented:
Don't think so, but perhaps another expert knows how to fool the access SQL interpreter...

Remains my Q: Why ?

Nic;o)
0
 
S2Commented:
do you mean like this?
SELECT (SELECT tbl2.fld1 FROM tbl2) FROM tbl1;

this would give you the value of tbl2.fld1 the number of records you have in tbl1.

if you need the values of tbl1 that are in the field that has the name in tbl2.fld1 then you will have to compose it in VBA, there is no escaping it.
0
 
matthewrobertsCommented:
Why don't you want to code in VBA and build the sql string on the fly??
0
 
pbryanCommented:
you could do it with a couple of queries, but like nico says "why?"

--------------------

Query 1:

Union all the fields together
Select "Field1" as FieldName, Field1 as FieldValue from TableOne UNION Select "Field2" as FieldName, Field2 as FieldValue from TableOne UNION Select "Field3" as FieldName, Field3 as FieldValue from TableOne

Query2:
Join union query to TableTwo on fieldname column (this filters the columns)

Query3:
Create crosstab based on Query 2, use FieldName as column header
0
 
geneheadAuthor Commented:
Accepting pbryan's comment as answer.  This is then kind of solution I was looking for.  I thought of UNION but didn't take it to the crosstab stage.  Good thinking pbryan.  Thanks - Gene
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now