I have two tables TPM_DataStore and TPM_Answer.
TPM_Datastore has all the recorded answers from an online form. The answers are 1,2,3, or 4. The form answers are each saved in their own column.. respectively named TPM1AS, TPM1BS, TPM1CS,.. (TPM #1, Answer A, S=Score).. TPM_Answer has the answer names and fieldvalue. I am trying to update the scores for each of the of TPM_Datastore columns. 1=5, 2=3, 3=2, 4=1. I can do this manually, but want to create a loop in which the "fieldvalue" is populated dynamically. I can't seem to get the syntax right. Any ideas? For some reason the CFIF doesn't recognize the variable. <cfquery datasource="noodlemetrics" name="getBMscores"> select questionaireid, tpm1as, tpm1bs, tpm1cs, tpm1ds, tpm2as, tpm2bs, tpm2cs, tpm2ds, tpm3as, tpm3bs, tpm3cs, tpm3ds, tpm4as, tpm4bs, tpm4cs, tpm4ds, tpm5as, tpm5bs, tpm5cs, tpm5ds, tpm6as, tpm6bs, tpm6cs, tpm6ds, tpm7as, tpm7bs, tpm7cs, tpm7ds, tpm8as, tpm8bs, tpm8cs, tpm8ds, tpm9as, tpm9bs, tpm9cs, tpm9ds, tpm10as, tpm10bs, tpm10cs, tpm10ds, tpm11as, tpm11bs, tpm11cs, tpm11ds, tpm12as,tpm12bs, tpm12cs, tpm12ds, tpm13as, tpm13bs, tpm13cs, tpm13ds, tpm14as, tpm14bs, tpm14cs, tpm14ds, tpm15as, tpm15bs, tpm15cs, tpm15ds, tpm16as, tpm16bs, tpm16cs, tpm16ds, tpm17as, tpm17bs, tpm17cs, tpm17ds, tpm18as, tpm18bs, tpm18cs, tpm18ds, tpm19as, tpm19bs, tpm19cs, tpm19ds, tpm20as, tpm20bs, tpm20cs, tpm20ds from tpm_datastore WHERE questionaireID = #url.questionaireid# </cfquery> <!-- Motive Questions; buyermotives is module 1--> <cfquery datasource="noodlemetrics" name="getFieldValues"> select fieldvalue from tpm_answer WHERE ModuleID = 1 AND Active = 1 </cfquery> <cfloop query="GetFieldValues"> <cfoutput> <cfif getBMscores.#fieldvalue# EQ 1> <cfquery datasource="noodlemetrics" name="updatescore"> update tpm_datastore SET #fieldvalue# = 5 WHERE questionaireid = #url.questionaireid# </cfquery> <cfelseif getbmscores.#fieldvalue# EQ 2> <cfquery datasource="noodlemetrics" name="updatescore"> update tpm_datastore SET #fieldvalue# = 3 WHERE questionaireid = #url.questionaireid# </cfquery> <cfelseif getbmscores.#fieldvalue# EQ 3> <cfquery datasource="noodlemetrics" name="updatescore"> update tpm_datastore SET #fieldvalue# = 2 WHERE questionaireid = #url.questionaireid# </cfquery> <cfelseif getbmscores.#fieldvalue# EQ 4> <cfquery datasource="noodlemetrics" name="updatescore"> update tpm_datastore SET #fieldvalue# = 1 WHERE questionaireid = #url.questionaireid# </cfquery> </cfif> </cfoutput> </cfloop> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4241 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
