Microsoft Access - Access Equivalent to Excel's Indirect function

Asked By rob on 16-Mar-11 06:26 PM
I have a table of factors (table1) with issueage as the key, columns represent duration and the field names are the duration number (1, 2, 3...120).  Looks like this (simplified):
Isage         1         2        3       4    ...   120
35          .4          .3        .2      .1        .01
36          .35         .25     .15     .05     .005
  I have a table with customer information (table2) that links to the factor table by issue age, and that table also has a field called duration.  Looks like this:
Record        Isage     Dur      ...
1            20       3  
2            35       2   
Several things easily work in Excel. Using the "&" feature in access I can create a string that gets me the right field, but I can't get it to "execute".  If in excel the string would get me what I need if used in the argument of the indirect function.  For example I can create the string "[table1]![" & [table2]![dur] & "]", for record 2 it generates "[table1]![1]" which should return .3.   I've tried eval, val, the 'c' functions, dlookup and combination with str.  How can I get a query to return .3 for record 2?  'table1' above is simplified, it contains thousands of lines and reworking isn't an option.
Pat Hartman replied to rob on 16-Mar-11 11:20 PM
Access is not a spreadsheet.  It is a relational database and in order to use it effectively, you must normalize your data.  Instead of having a table with a repeating group of 120 columns, you should change the columns to rows.  Once you do that, your queries will be a cinch.  As it stands now, you'll be forever writing code to work with these tables.

You should end up with a table that looks like this:

Isage, DurNum, DurVal
35, 1, .4
35, 2, .3
35, 3, .2
35, 4, .1
...
35, 120, .01
36, 1, .35
36, 2, .25
36, 3, .15
36, 4, .05
...
36, 120, .005

To make this normalized table, you have two choices.  Either 120 queries that transpose one column at a time or a code loop that builds the rows by looping through the Fields collection of the tabledef and inserting a row for each column in a new table.
rob replied to Pat Hartman on 17-Mar-11 10:09 AM
Thnks for your reply.  Transposing will turn the existing table with thousands of rows into one with millions and affect other applications that use the file, not a good option.  A multi-field choose function setup was my backup plan until vb code can be written.  Having an indirect function would make access more powerful and has nothing to do with spreadsheet/database format but basic transformation of text into an executable.
Pat Hartman replied to rob on 17-Mar-11 05:08 PM
Even a million row table can be searched with no more than 8 physical reads as long as an index is used.  Number of rows is not a reason to denormalize a schema.

Access can do what you want with the eval() function but not in a query since the SQL language is not a true programming language.  In a query all columns are fixed ahead of time (this is not an Access/ACE rule, it is a relational database rule and you will have the same problem with Oracle, SQL Server, DB2, etc.) and cannot be modified on the fly.  To create a query with variable columns, you would need to build it with VBA.