?
Solved

Write a cf query to sum multiple records?

Posted on 2003-03-10
19
Medium Priority
?
366 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

770 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