Need help in choosing random records

[jimger]

Hi -
I'm fairly new to Access, and have been given a task to randomly select records for quality control. I work in a human services agency, and our supervisors want to pick a random selection of cases from each case manager for review. I've built a query with the following columns:
Caseload, Case Number, Case Name. Each case manager has a caseload number, and ends up with several cases on the query. I used the Rnd() expression with the Case Number field to return the records in random order. I built another query based on this query to select the top values. I can select the top values for each caseload if I use a parameter in the caseload field, but that means I'll have to run the query for each of the 20 or so caseloads each month. I know there's an easier way to do this, I just don't know what that is.
Thanks in advance for any help.

 

[PHV]

Say you have a query named qryGetRandomCases with the following columns:
Caseload, Case Number, Case Name, RandomNumber

Create a new saved query named, say, qryGetRandomCasesRanked:
SELECT A.Caseload, A.[Case Number], A.[Case Name], Count(*) AS Rank
FROM qryGetRandomCases A INNER JOIN qryGetRandomCases B
ON A.Caseload=B.Caseload AND A.RandomNumber<=B.RandomNumber
GROUP BY A.Caseload, A.[Case Number], A.[Case Name]

Now, to get say 25 random cases for each caseload:
SELECT Caseload, [Case Number], [Case Name]
FROM qryGetRandomCasesRanked
WHERE Rank<=25

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jimger]

PH -
Thanks for the quick reply. I ran into a couple of problems with your answer, probably because of my inexperience. I adapted your SQL for the second query to fit my data:
SELECT A.CASELOAD, A.CASE_NBR, A.Name, Count(*) AS Rank
FROM Renewal AS A INNER JOIN Renewal AS B ON (A.RandomNum<=B.RandomNum) AND (A.CASELOAD=B.CASELOAD)
GROUP BY A.CASELOAD, A.CASE_NBR, A.Name;

It returns the records ordered first by caseload (good), then by case number (bad if we're picking random cases). The numbers in the Rank column are indeed random - 4 digit numbers in the 1900-2099 range.

When I tried the third query to select the top 25 (or 3 in my case), I get no rows at all. Here's my SQL statement:

SELECT CASELOAD,[CASE_NBR],[Name]
FROM Renewalrank
Where Rank<=3;
Where have I gone wrong?