SumIF Statement in Query

[athyeh]

Hello,



I am capturing production right now as operators scan in barcodes into the database, and want to compare that to an hourly goal that is being captured with each record.

So each record captured looks like this:

Johnny, 1 Unit Produced, Goal 35, Time Added (m/d/yyyy h:mm/ss AM/PM)

Now I am trying to build a query that I can run a report off of that shows the total amount they did vs. their daily goal, and currently have this:

SELECT Format([Time Added],"m/d/yyyy") AS Expr1, tblMainDB.Operator, Count(tblMainDB.LID) AS CountOfLID, tblMainDB.Process, Sum(tblMainDB.Goal) AS SumOfGoal
FROM tblMainDB
GROUP BY Format([Time Added],"m/d/yyyy"), tblMainDB.Operator, tblMainDB.Process
HAVING (((tblMainDB.Process)="Authenticate"));

This however sums up All the values in the Goal field. I need it to show from the hours of 9 AM - 10 AM Operators were ringing their production units in under a goal of 35 or 26 or 15, and to add those numbers up over the hour. My first thought is a SUMIF function? Does anyone have any ideas?

Thanks!

[June7]

Calculate a field that has the hour as a value that can be grouped on as well as the m/d/yyyy, operator, process.

Hr: Hour([Time Added])

[athyeh]

Solved. Thank you.