Solved

# Aggregation SQL statement (sum) with more than one criteria

Posted on 2012-08-18
Medium Priority
622 Views
Hi:
I have table tblLate, with columns (EmpID, LateDate,  LateHours and LateExcuse)
I need to calculate sum of "LateHours " where "LateDate" is between two dates and no excuse ("LateExcuse" = false), for each employee .
The next snippet of code brings back multiple rows for each emp. Where I assume one row only for each!
Could you give me a code brings one row for each employee with the sum of all his late hours ?
"SELECT tblLate.EmpID, tblLate.LateDate, Sum(tblLate.LateHours) AS Sum¿¿LateHours, tblLate.LateExcuse
FROM tblLate
GROUP BY tblLate.EmpID, tblLate.LateDate, tblLate.LateExcuse
1.JPG
0

LVL 52

Expert Comment

ID: 38308442
Try with:

"SELECT tblLate.EmpID, Sum(tblLate.LateHours) AS Sum_LateHours
FROM tblLate
GROUP BY tblLate.EmpID

/gustav
0

LVL 31

Accepted Solution

hnasr earned 2000 total points
ID: 38308944
GROUP BY After WHERE

compare:
table: a(f1 number, dd Date/Time, price2 number, ck Boolean)
form: ae controls txtDate1 Short Date, txtDate2 Short Date

``````SELECT a.f1, Sum(a.price2) AS SumOfprice2
FROM a
WHERE (((a.dd) Between [forms]![ae]![txtDate1] And [forms]![ae]![txtdate2]) AND ((a.ck)=False))
GROUP BY a.f1;
``````
0

Author Closing Comment

ID: 38309469
wonderful
0

## Featured Post

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month16 days, 22 hours left to enroll