Hi everyone. I am having an issue.
The facility I work at would like to conduct employee surveys by choosing five random employees from each of our units.
I have a query to sort the records in the employee table in a random order by location and select the Top 5 results from each unit.
However they also want to wait at least 60 days before the same person is eligible to be selected again.
To facilitate this, I've created a new table to store the results of the survey and included the survey date. This is the query as it stands:
The issue I am having in testing is that if one or more employees who have been surveyed within the last 60 days get randomized into the Top 5, the query will exclude those employees from the results and return that many fewer Top results for that unit. (I.E.: if two ineligible employees get randomized into the top five, the query only returns the Top 3 results)
How can I structure this so that the query will skip over employees as needed and still return 5 results?
The facility I work at would like to conduct employee surveys by choosing five random employees from each of our units.
I have a query to sort the records in the employee table in a random order by location and select the Top 5 results from each unit.
However they also want to wait at least 60 days before the same person is eligible to be selected again.
To facilitate this, I've created a new table to store the results of the survey and included the survey date. This is the query as it stands:
SQL:
SELECT LOCA, EmpID, FN, LN, [ADM DATE]
FROM CENSUS t
WHERE EmpID IN
(
SELECT TOP 5 EmpID
FROM CENSUS
WHERE LOCA=t.LOCA
AND NOT EXISTS
(
SELECT EmployeeID FROM
tblEmpSurveys as t2
WHERE t2.EmployeeID=t.EmpID AND SurveyDate>=Date()-60
)
ORDER BY Rnd(-Timer()*[EmpID]), LOCA
)
ORDER BY LOCA, LN, FN;The issue I am having in testing is that if one or more employees who have been surveyed within the last 60 days get randomized into the Top 5, the query will exclude those employees from the results and return that many fewer Top results for that unit. (I.E.: if two ineligible employees get randomized into the top five, the query only returns the Top 3 results)
How can I structure this so that the query will skip over employees as needed and still return 5 results?
