Using a variable as a SELECT field

[tjIII]

I'm trying to get shipping costs for different weights and zip codes. I query one table to get a zone from the user provided zip code. That works. I then want to use that zone to retreive the shipping cost for a specific weight from another table. I set the zone as a variable and use it as follows: SELECT #Zone# FROM Ground WHERE Wt=#weight# . If I use the actual zone name instead of the variable, everything works right. When I use the variable, however, and try to output the reslut with the #Zone# variable, I get the original name of the zone field name, not the rate that is listed under that field name

Is it proper to use a variable the way I have? If so, how do you access the result? If not, is there another way to get a field name for a query from another query? Thanks.

[Dan_Bracuk]

Normalize your database and you won't have problems like this.

[Guest · Accepted solution]

You can use a variable to dynamically change the select field but this is seldom a good idea.
Here's how you would do it in this case:

<CFQUERY name="MaintenanceNightmare" datasource="YOUR_DATA_SOURCE">
SELECT
#Zone# AS ShippingEstimate
FROM
Ground
WHERE
Wt=#weight#
</CFQUERY>

<CFOUTPUT>
<h3>Shipping #weight# kilograms, to zone: #Zone#, will cost #MaintenanceNightmare.ShippingEstimate# Euros.</h3>
</CFOUTPUT>


The suggestion, to normalize your database, was a good one.

[tjIII]

Thanks, that solved the problem. I'm definitely an amature, but I do understand normalization to a degree. However, these are tables I download from FedEx in Excel format and convert to Access with only a few minor changes. These tables change periodically, and I was hoping I could update them with only a minimum amount of work.