Write a cf query to sum multiple records?
Posted on 2003-03-10
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.