Referencing a field name via a variable

[antvon]

Hi all

I'm looking for a way to reference a table/query field
name by using a variable.
Lets say I have a table with the following fields:
Customer
Budget1
Budget2
Budget3
.
.
Budget12

Where Budget1 is the Budget target for financial period 1

What I would like to do is create a query that refers to the budget field using a variable. eg
CurrentPeriodBudget:[Budget] & X

Where X = 2 for example

Does anyone know of a way to do this if at all possible?
Would I maybe need to use ADOX?

Thanks in advance

 

[JonFer]

Can you normalize your table (i.e. make the date/month a field and have a single budget amount field - this creates more records but is much easier to query)? If so, you won't need to use a variable to change which field you want.

If you don't have control of the table structure then you have several options...

1. Use a function to modify the SQL of a defined query and change which field is pulled out.

2. Build a normalizing table (12 rows with 13 columns of 0's and 1's) and use it in a select query to generate a normalized view of the table. Select from this view.

3. Create a Union query to generate a normalized view of the table and then select from the Union query.


John

http://www.interautomateconsulting.com

 

[antvon]

Hi John

Thanks for your reply.
Unfortunately the table was fixed.

I went with your first alternative suggestion and
modified the defined query using a querydef object.
This did give me the flexibility I was looking for.

Thanks for your efforts again.

regards
Anton