Looping and setting cfset values (from a query)

[Guest]

Stuck on how to get this to work.

How would I go about looping and setting these values instead of doing them individually?

The query this <cfset> references works fine and will work but repeats...

<cfset ans1 = (qryGetPcts1.q1Ct / qryGetCounts.qID2) * 100>
<cfset ans2 = (qryGetPcts2.q2Ct / qryGetCounts.qID2) * 100>
<cfset ans3 = (qryGetPcts3.q3Ct / qryGetCounts.qID2) * 100>
<cfset ans4 = (qryGetPcts4.q4Ct / qryGetCounts.qID2) * 100>
<cfset ans5 = (qryGetPcts5.q5Ct / qryGetCounts.qID2) * 100>

So I'd like to loop and set them all instead...

<cfloop index="i" from="1" to="27">
<cfset ans#i# = (qryGetPcts#i#.q#i#Ct / qryGetCounts.qID2) * 100>
</cfloop>

But it's responding with this...

Invalid CFML construct found on line 196 at column 19.
ColdFusion was looking at the following text:
#

I'm sure I'm missing something simple b/c this must be possible? Can I use a cfset in a loop?

Help is appreciated

[Dan_Bracuk]

A better approach might be to figure out a way to get your data without running 27 queries.

[Stressed_Simon]

In the main everything is in CF is in a struct. Armed with this information you use array notation to help you.

<cfloop index="iCount" from="1" to="27">
<cfset variables["ans" & iCount] = (variables["qryGetPcts" & iCount]["q" & iCount & "Ct"] / qryGetCounts.qID2) * 100>
</cfloop>

[Stressed_Simon]

But as Dan Bracuk rightly points out, you can probably generate this in the db much cleaner and more efficiently when you get the original query.

[cf_dev2]

Not to keep harping on this point, but as mentioned here and in another thread, using a more normalized structure would simplify your code and queries. This design makes things more complicated than they need to be.

[Guest]

Thanks Simon/cf_dev2, I hadn't really looked into this much and threw it together. I'll take a second look.