Drawing dynamically defined fields in MS Access

Question

I have no idea if this is possible - and suspect it may not be.

I have a table in MS Access 2003 that lists some fields that are present in another table in the database. What I want to do is draw the data from the other table but in the select statement define the fields to draw by using the values in the first table.

For example

Table 1
[Sequence]  [Name]
1           CustomerId
2           CustomerName
3           CustomerBirthday

Table 2
[CustomerId]  [CustomerCode]  [CustomerName]  [CustomerType]  [CustomerBirthday]
1             A123            Andrew          A1              Aug
2             A122            Bob             A2              Nov
3             A133            Charles         A1              Jan
4             A153            Diane           A5              Mar

Required Output, using the information defined in table 1:
1  Andrew   Aug
2  Bob    Nov
3  Charles  Jan
4  Diane    Mar

The required output could be generated as follows:

SELECT CustomerId, CustomerName, CustomerBirthday FROM Table2

However I want to be able to change the fields and so would like to do something like:

SELECT [field name in table1 Where Sequence=1], [field name in table1 Where Sequence=2], [field name in table1 Where Sequence=3] FROM Table2

I know I could do it in code, but wondering if there is a way to do it in pure SQL so I can stick it into a normal query.

Answer 1

The solution is clearly to build your SQL query "on the fly" with a VBA function. According to me, table1 should have an extra column containing the name of the table that holds the fields:

Table 1
[tableName] [Sequence]  [Name]
table2      1           CustomerId
table2      2           CustomerName
table2      3           CustomerBirthday
...
tablen      1           field 1
tablen      2           field 2
...
tablen      i           field i

The code that will generate the SQL query will then look like that:

Public function fieldQueryFromTable1(x_tableName as String) as string

Dim rsTable1 as DAO.recordset, _
    m_fieldQuery as String, _
    m_tableQuery as string, _
    a_fieldNames() as string

m_tableQuery = "SELECT name FROM table1 WHERE tableName = """ & x_tablename & """ ORDER BY sequence"

set rsTable1 = currentDb.openRecordset m_tableQuery, dbOpenDynaset, dbReadOnly
a_fieldNames = rsTable1.getrows()   
    'generate a 2 dim array a_fieldName(fieldName, fieldValue)'

set rsTable1 = Nothing

m_fieldQuery = join(a_fieldNames(0),",")   
    'a_fieldNames(0) is a 1 dim array that contains the field names'
    'm_fieldQuery is a string that looks like "field1,field2, ..., fieldi, ..."'


if m_fieldQuery <> "" then
    m_fieldQuery = "SELECT " & m_fieldQuery & " FROM " & x_tableName
    'SELECT field1,field2, ..., fieldi, ... FROM Tablen'
Endif

fieldQueryFromTable1 = m_fieldQuery
end function

This was written on the fly, just for you to get the principle. No error handler, no syntax check, nothing! We usually do similar things with ADO recordsets, so I am not quite sure of the 'getRows' DAO recordset method bt, according to the help, it works like the ADO one.