Query to choose 5 records at random while excluding records based on criteria.

[TonyD1016]

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:

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?

 

[Micron]

I'm thinking that you simply use a query that excludes those who were chosen in the past 60 days. You then use your random and top 5 methods on this query to get what you want from this subset rather than querying a table.

 

[TonyD1016]

I'm thinking that you simply use a query that excludes those who were chosen in the past 60 days. You then use your random and top 5 methods on this query to get what you want from this subset rather than querying a table.

The first time I tried this at first and the resulting randomize would only return results from a single unit. But I went back just now and tried it again anyway, and it appears to be doing exactly what I need it to do.

I don't know what I did differently, but the code works as expected so I'm going to stop questioning it. Thank you very much for the reply.

 

[Micron]

You're welcome, although I don't think I deserve to have my post marked as the solution if it wasn't. You could mark your last post and that would still mark the thread as solved. Doesn't really matter though, I guess.