SQL dynamic field name

[lloydjs]

How do you name an SQL field according to a field value found in a previous query? I am trying to give a descriptive name for a column in an exported file that changes depending on the thing looked up. The first queries get the serial number, test date and date, the second query puts the test data from these queries into different columns for use with Microsoft Chart. I would like to change the names of the columns when they are exported to an Excel spreadsheet to reflect the number and date of the test results in the proper column.

Thanks

 

[KenReay]

Hi

are you building the SQL in code?

try something like

strSQL = "SELECT field1 As " & strMyDescriptiveName & ", ...
DoCmd.RunSQL strSQL

Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[scriverb]

Could you give an example of the fields being pulled by both queries and then what you want the columns to be called. This can be done but I need a little more specific information on your request. Bob Scriver

 

[lloydjs]

The first query returns the fields with the serial number and the date it was tested:

Field Value
SN 1234
TDate 2/1/02 8:18AM
Result 1.234

The second query returns about 200 records for each test date containing the individual time records with the format:
Field Value
SN 1234
TDate 2/1/02 8:18AM
RTime 0.000
Temp 300.123
Press1 1000.12
Press2 1123.45

I would like the exported format to be
Field Name R1 R2 R3 etc.
"RTime" 0.000 0.001 0.002
"1234 2-1-02 8:18 Temp" 300.123 295.120 280.001
"1234 2-1-02 8:18 Press1" 1000.12 930.12 850.95
"1234 2-1-02 8:18 Press2" 1123.45 1007.67 900.17
"2234 2-1-02 8:18 Temp" 305.123 290.120 282.001
"2234 2-1-02 8:18 Press1" 1120.45 1017.67 909.17
"2234 2-1-02 8:18 Press2" 1323.45 1107.67 990.17
"3234 2-1-02 8:18 Temp" 305.123 290.120 282.001
"3234 2-1-02 8:18 Press1" 1120.45 1017.67 909.17
"3234 2-1-02 8:18 Press2" 1323.45 1107.67 990.17

I would like to do this within the query if possible. At this point I am using generic names like "Oxy 1", "Helium 1","Oxy 2", "Helium 2". I'm sure you can see where this can get confusing if somebody's trying to do manipulations outside the application.

Thanks