Solved

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

Posted on 2012-08-18
562 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
Question by:M_SOLAIMAN

LVL 48

Expert Comment

Try with:

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

/gustav
0

LVL 30

Accepted Solution

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

wonderful
0

## Featured Post

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.