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

TonyD1016

Board Regular
Joined
Nov 18, 2021
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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:

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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 1
Solution
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. :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top