Aggregation SQL statement (sum) with more than one criteria

Posted on 2012-08-18
Medium Priority
Last Modified: 2012-08-19
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
HAVING (((tblLate.LateDate) Between [Forms]![frmReportsMenu]![DateFrom] And [Forms]![frmReportsMenu]![DateTo]) AND ((tblLate.LateExcuse)=False));"
Help me please.
Question by:Mohammad Alsolaiman
LVL 52

Expert Comment

by:Gustav Brock
ID: 38308442
Try with:

"SELECT tblLate.EmpID, Sum(tblLate.LateHours) AS Sum_LateHours
FROM tblLate
GROUP BY tblLate.EmpID
WHERE tblLate.LateDate Between DateValue([Forms]![frmReportsMenu]![DateFrom]) And DateValue([Forms]![frmReportsMenu]![DateTo]) AND tblLate.LateExcuse=False;"

LVL 31

Accepted Solution

hnasr earned 2000 total points
ID: 38308944

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
WHERE (((a.dd) Between [forms]![ae]![txtDate1] And [forms]![ae]![txtdate2]) AND ((a.ck)=False))
GROUP BY a.f1;

Open in new window


Author Closing Comment

by:Mohammad Alsolaiman
ID: 38309469

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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

864 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