Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Write a cf query to sum multiple records?

Posted on 2003-03-10
19
Medium Priority
?
374 Views
Last Modified: 2013-12-24
I have an Access table that has multiple records for each employee.
Each of these records has a field named HOURS, it is a number type.

My question is, how can I write a cf query to sum the HOURS column for each employee, and if that sum is greater than 50, then edit the last record so that it makes the total sum equals 50?

In other words, if I had five records that belong to Joe Money, and each record has 10 in the HOURS column, except for the last record having 12 in the Hours column; how would I write the query so that it gets the total, and the amount that it needs to change to get to 50 (in this case - 2), then edits the last record for the HOURS column to be 10?

Any help is appreciated.
0
Comment
Question by:g118481
  • 7
  • 6
  • 3
  • +3
19 Comments
 
LVL 5

Expert Comment

by:JimV_ATL
ID: 8105406
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>
0
 
LVL 10

Expert Comment

by:substand
ID: 8106469
sql does work on access
0
 
LVL 5

Expert Comment

by:JimV_ATL
ID: 8106923
My statement was "I don't know if _this_ sql" will work on Access.  Access SQL is lest robust than T-SQL.  
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 10

Expert Comment

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

0
 
LVL 10

Expert Comment

by:substand
ID: 8106988
however, if you were using pl/sql from oracle, then obviously access would not support it.
0
 
LVL 5

Expert Comment

by:JimV_ATL
ID: 8107673
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


0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8107847
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
     


0
 
LVL 1

Author Comment

by:g118481
ID: 8111491
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.
0
 
LVL 10

Expert Comment

by:substand
ID: 8113363
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...

0
 
LVL 1

Author Comment

by:g118481
ID: 8115591
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')"

0
 
LVL 10

Expert Comment

by:substand
ID: 8120520
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.



0
 
LVL 5

Accepted Solution

by:
JimV_ATL earned 200 total points
ID: 8121114
This is the  divided into three separate queries.  Perhaps this will work.

<cfloop query="getrecords">

<cfquery name="get_record_to_update" datasource ="trs_codes2">
select max(id) as max_id
from c
where emp = '#getrecords.emp#'
</cfquery>

<cfquery name="get_hours" datasource ="trs_codes2">
select sum(hours) - (#getrecords.hrs# - 50)
from c
where emp = #getrecords.emp#
</cfquery>

<cfquery name="update" datasource ="trs_codes2">
update c
where emp = #getrecords.emp# and id = #get_record_to_update.max_id#
</cfquery>

</cfloop>
0
 
LVL 5

Expert Comment

by:JimV_ATL
ID: 8121120
I forgot to put the single quote around #getrecords.emp# in the second and third queries.
0
 

Expert Comment

by:blueweb
ID: 8126894
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>
0
 

Expert Comment

by:bobdinski
ID: 8138134
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  
0
 
LVL 10

Expert Comment

by:substand
ID: 8154955
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.
0
 
LVL 5

Expert Comment

by:JimV_ATL
ID: 8155233
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.
0
 
LVL 5

Expert Comment

by:JimV_ATL
ID: 8155238
0
 
LVL 1

Author Comment

by:g118481
ID: 8358376
Thanks you sir!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Loops Section Overview
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month10 days, 6 hours left to enroll

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question