Sum based on date range

[Rohn]

I need to measure the tallies in the "SEVERITY" column over the last six
months from the date range in the "START_DATE" column by Employee (EMP_ID).
I hope that makes since?

Table Structure:
UNIQUE_ID, EMP_ID, START_DATE, END_DATE, NOTES, SEVERITY, N_DAYS

I need to be able to query by EMP_ID for the total number SEVERITY over the
last 180 days from today backwards. It will be a rolling measurement of
severity based on the START_DATE issue. Output takes 1250 records and
groups them by EMP_ID to look like:

EMP_ID SEVERITY
0284 9
0917 11
1148 3
1375 0
1480 6
etc..... I want to be able to run a query daily, when an employee reaches 5
points (SEVERITY) as counted in the preceding 180 days (START_DATE), I will
manually generate a letter to the employee but I need the data to be
summerized so I can see it. I also looked the Northwind.mdb & Date_97.mdb
by Thomas M. Brittell with no luck.Thanks for your help or advice!

 

[Guest]

You did not say the name of the table so I named it Rohn. This will display
EmpID that has cumulative severity of 5 or greater in the last 180 days.

SELECT Rohn.EMP_ID, Sum(Rohn.SEVERITY) AS SumOfSEVERITY
FROM Rohn
WHERE (((Rohn.START_DATE) Between Date()-180 And Date()))
GROUP BY Rohn.EMP_ID
HAVING (((Sum(Rohn.SEVERITY))>=5));