Aggregation SQL statement (sum) with more than one criteria

Posted on 2012-08-18
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:M_SOLAIMAN
    LVL 48

    Expert Comment

    by:Gustav Brock
    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 30

    Accepted Solution


    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 ((
    GROUP BY a.f1;

    Open in new window


    Author Closing Comment


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    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 (, 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.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now