Write a cf query to sum multiple records?

I don't know if this sql will work on Access, but it's worth a try.

<!---
First get a list of all of the employees with more than 50 hours.
--->

<cfquery name="getrecords" datasource = "mydsn">
select employee_id, sum(hours)
from employee_hours
where 0=0  -- put here as a place holder for additional criteria
group by employee_id
having sum(hours) > 50
</cfquery>

<!---
Next loop through those employees and change the last record
--->

<cfloop query="getrecords">
update employee_hours
set hours = hours -
     (-50 +
          (
          select sum(hours)
          from employee_hours
          where employee_id = #getrecords.employee_id#
          -- and addtional criteria
          )
     )
where employee_id = #getrecords.employee_id#
and record_id =
     (select max(record_id)
     from employee_hours
     where employee_id = #getrecords.employee_id#
     -- and addtional criteria
     )
</cfloop>

sql does work on access

My statement was "I don't know if _this_ sql" will work on Access.  Access SQL is lest robust than T-SQL.  

well, sql just stands for "structured query langauge," which access supports.  so as long as you are using standard sql, access should support it.  

however, if you were using pl/sql from oracle, then obviously access would not support it.

From "Fundamental Microsoft Jet SQL for Access 2000":

The particular dialect of SQL discussed in this article applies to version 4.0 of the Microsoft Jet database engine. Although many of the SQL statements will work in other databases, such as Microsoft SQL Server™, there are some differences in syntax. To identify the correct SQL syntax, consult the documentation for the database system you are using...

ANSI stands for the American National Standards Institute, which is a nationally recognized standards-setting organization that has defined a base standard for SQL. The most recently defined standard is SQL-92, and Access 2000 has added many new features to conform more closely to the standard, although some of the new features are available only when you are using the Jet OLE DB provider. However, Access has also maintained compliance with previous versions to allow for the greatest flexibility. Access also has some extra features not yet defined by the standard that extend the power of SQL.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

if you can do it in access, then make coldfusion as simple as you can.

the best thing about access, you can query a query as you can query a table.


so inside access, do the whole work using query design

query_1
--------
select employee_id, sum(hours) as no_hours
from   employee_hours
group by employee_id

query_2
--------
select employee_id, no_hours
from   query_1
where  no_hours >  50

query_3
--------
select  A.employee_id , B.no_hours  
, max(A.rec_id) as rec_id
from   employee_hours A, query_2 B
where  A.employee_id  = B.employee_id  
group by A.employee_id , B.no_hours  

( note sure about query_3 but it link between query_2 and main table to get last record for update )


at coldfusion
<cfquery ....
  select * from query_3
>

<cfloop....
   update statment
     

JimV_ATL,

I edited your suggestion to fit my field and table names.  Here is the code:
<cfquery name="getrecords" datasource ="trs_codes2">
select emp, sum(hours) as hrs
from c
where 0=0  
group by emp
having sum(hours) > 50
</cfquery>

<cfloop query="getrecords">
<cfquery name="update" datasource ="trs_codes2">
update c
set hours = #getrecords.hrs# - (-50 + (select sum(hours) from c where emp = #getrecords.emp#))
where emp = #getrecords.emp# and id = (select max(id) from c where emp = #getrecords.emp#)
</cfquery>
</cfloop>

But I am getting this error message.  Can you advise on this problem?
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression '60.0 - (-50 + (select sum(hours) from c where emp = BOB JOHNSON))'.


SQL = "update c set hours = 60.0 - (-50 + (select sum(hours) from c where emp = BOB JOHNSON)) where emp = BOB JOHNSON and id = (select max(id) from c where emp = BOB JOHNSON)"

Thanks.

you have to put the name in single quotes, like '#getrecords.emp#'... also you'll need to make one of those - signs a plus, i would think, since that will set them to 170...

I have added the ' marks around the #getrecords.emp#, but am now getting this error.

Please advise.

ODBC Error Code = S1000 (General error)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

SQL = "update c set hours = '100.0' - (50 + (select sum(hours) from c where emp = 'BOB JOHNSON')) where emp = 'BOB JOHNSON' and id = (select max(id) from c where emp = 'BOB JOHNSON')"

try removing the single quotes from '#getrecords.hrs#'.

that query doesn't make much sense to me, but if it works, it works.  i would sugguest you use 3 queries.  1 where you get the employee, 2 where you get their hours, and a 3rd where you change thier hours.

I forgot to put the single quote around #getrecords.emp# in the second and third queries.

A few assumptions used in my code:
- all records have a text-based id (eg. CF UUID)
- if the number of hours exceeds 50 by more than the last record's value, the last record's new value will be a negative number

So, For each employee, do the following:

<!--- The current employee id will come from some other query, I'm setting it here --->
<cfset variables.currentemployee_id = "test123">

<!--- get sum of hours for current employee --->
<cfquery name="emptotalhours" datasource="#dsn#">
     select     sum(hours) AS totalhours
     from     employeehours
     where     employee_id = '#variables.currentemployee_id#'
</cfquery>

<!--- get separate hours-records for current employee --->
<cfquery name="emphours" datasource="#dsn#">
     select     id, hours
     from     employeehours
     where     employee_id = '#variables.currentemployee_id#'
</cfquery>

<!--- output sum of hours --->
<cfoutput query="emptotalhours">
     <p>
          #emptotalhours.totalhours# total hours
     </p>
</cfoutput>

<!--- if sum of hours exceeds max value (in this case 50) --->
<cfif emptotalhours.totalhours GT 50>
     <!--- calculate how many hours we need to subtract --->
     <cfset variables.toomuch = emptotalhours.totalhours - 50>
     <!--- set new value for last record for current employee --->
     <cfoutput query="emphours" startrow="#emphours.recordcount#">
          <!--- calculate new value --->
          <cfset variables.newlastvalue = emphours.hours - variables.toomuch>
          <!--- update DB --->
          <cfquery name="updatehours" datasource="#dsn#">
               update     employeehours
               set          hours = #variables.newlastvalue#
               where     id = '#emphours.id#'
          </cfquery>
     </cfoutput>
</cfif>

If your query is not dependent on dynamically updated data from a cfm form, you could just use a sum function based on a query inside Access, then pass that query to the table/column lookup value for that field, next simply query that column on your cfm form.  This will help increase performance too, on large recordsets.  That's what I do.
If it is dynamicly dependent a work around solution to get yourself going would be to add a level(another pass info into cfm page) beetwen getting the data, pass this data to a new xref table, then make calculations inside access.
And yes, Microsoft has it's own version of SQL which is based on SQL, with other crap added in, so you have to be careful when writing complicated SQL statements on your cfm from.  I suggest downloading and converting to MySQL...it's FREE, has better structure, and has greater performance, and WAY more type definitions, which can be fun.

Hope this helps.  

-Bob  

not that this is related to the thread- but mysql has greater performance than MSSQL 2000?

where can I find documentation on that?  Every comparison I've seen between MSSQL 2000 and anything else shows MS blowing the other away, including oracle db.

Here's an article that compares the various datbases.  It's worth noting, however, that when this article was written, MySQL wasn't ACID compliant.  I wouldn't be at all surprised that in its quest to become ACID compliant, MySQL lost a significant amount of speed.

http://www.eweek.com/article2/0,3959,293,00.asp

Forgot the article.

Thanks you sir!