Create Query based on field names stored in a separate table

[pubdude2003 via AccessMonster.com]

Hi all, have an odd problem which I thought would be dead easy but has become
an afternoon project.

I would like to create a Select Query on a table containing numerous fields.
I want to to use a separate single record table to 'map' the field names from
the [Main Table] table into the Select Query (the Mappings table is
maintained from a form). Because of the specific application I cannot rename
fields or make a new table. My first thought was to use a DLookup to grab the
values from Mappings (the field names of the Main Table table) to select the
values for the query, but no soap.

SELECT (DLookUp("MasterID","Mappings")) AS MasterID, (DLookUp("Field1",
"Mappings")) AS Field1
FROM [Main Table];

Any ideas anyone?

 

[Allen Browne]

Use VBA code to build the SQL statement. You can alias the fields so they
end up with the names you need.

The final string will look something like this:
strSql = "SELECT " & DLookup("OutputField", "Mappings", "InputField =
'MasterID'") & " AS MasterID, " & DLookup("OutputField", "Mappings",
"InputField = 'Field1'") & " AS Field1, " & ...

Once you get the string you can write it to Query1 like this:
CurrentDb.QueryDefs("Query1").SQL = strSql
or you could assign it to the RecordSource of a form any time:
Forms!Form1.RecordSource = strSql
or to the RecordSource of a report (in its Open event.)

If there are lots of fields, it would be more efficient to OpenRecordset and
loop through them to build the SQL string rather than run lots of DLookups.

 

[pubdude2003 via AccessMonster.com]

As always Allen, many thanks for your help.