Access queries and/or CF arith. expressions

[Guest_imported]

I'm still relatively new to coding CF, but here's what I'm trying to do - I'm trying to code one of my templates so that it retrieves a column of numbers from a database table, sum them all dynamically, have the same happen to another column, then have one of those sums divided by the other, also in real time dynamically. Any suggestions on how I would build a query like this in MS Access, or just by coding it with CF? Thanks.

 

[Guest_imported]

Try this (there is probably a more effiect way to do this using SQL, but I am not as confident with SQL statements, and did not have time to do a test, sorry):

<cfquery name=&quot;theQuery&quot; DATASOURCE=&quot;#ODBC_DATASOURCE#&quot;>
Select column1, column2
FROM Database
Where this = that
</cfquery>

<cfset var1=0>
<cfset var2=0>
<cfoutput query=&quot;theQuery&quot;>
<cfset var1 = var1 + column1>
<cfset var2 = var2 + column2>
</cfoutput>

<cfset theAnswer = var1/var2>

Hope this helps. There is a sum function for sql, which I think would be more efficient though