Exact Date Criteria is hit

[ad.business]

Hello, I need help with the following:

I have a table called tblStats, with the following fields

-Date (mm/dd/yyyy)
-Month (mmmm)
-Name
-Sales % to goal (updated via update query)

I built a select query for each month for the last 6 months and then a main query to view the last 6 months of stats. I have the query assign a #1 to each of the months that >=80% to goal was achieved. I count how many instances this has occurred in the last 6 months (easy). What I need help with is how to calculate when a person has hit 80% to goal for 4 of the 6 months and show the date that it happened? Maybe I need some running calculation? I'm clueless. Any help with the process I should take would be extremely helpful. Thanks.

 

[apr pillai]

I think you can solve this by creating a Total Query using the Main Query as Source, by filtering on #1.

Group it on Employee, Sum on #1 column, use Max on Date Column to pick the latest date, filter on SumofStatus >= 4 cases.

Sample SQL:

SELECT MainQ.EmployeeID, Max(MainQ.StatDate) AS MaxOfStatDate, Sum(MainQ.Status) AS SumOfStatus
FROM MainQ
GROUP BY MainQ.EmployeeID
HAVING (((Sum(MainQ.Status))>=4));