Query to randomly select records

[Leesha]

Hi,

Our office needs to do an audit every quarter that is random based on the patients who are admitted to the agency. For example, this quarter there are 353 patients who are eligible to be picked. I need a query to randomly choose 25 names. What would be the best approach towards doing this or is it possible?

Thanks,
Leesha

[HansV]

For the following, you can use any number field in the table; let's say that you use a field named ID.

- Create a query based on the table or query containing the eligible patients.
- Add the fields that you need, or * to return all fields.
- Add a calculated column

R: Rnd([ID])

- Clear the Show check box for this column.
- Click in an empty part of the upper half of the query design window.
- In the Properties window, set the Top Values property to 25.

Note: the query will return a different selection each time you run it.

[Leesha]

I think I'm doing something wrong since it returns the same people each time. When I clear the checkbox for the calculated column and save the query and reopen it its gone????

Here's my sql:

SELECT TOP 25 tblCRRCensusCrosstab_Temp.ID, tblCRRCensusCrosstab_Temp.client_no, tblCRRCensusCrosstab_Temp.admit_no, tblCRRCensusCrosstab_Temp.off_code, tblCRRCensusCrosstab_Temp.OfficeName, tblCRRCensusCrosstab_Temp.first_name, tblCRRCensusCrosstab_Temp.last_name, Rnd([ID]) AS R
FROM tblCRRCensusCrosstab_Temp;

Leesha

[HansV]

Try this:

SELECT TOP 25 tblCRRCensusCrosstab_Temp.ID, tblCRRCensusCrosstab_Temp.client_no, tblCRRCensusCrosstab_Temp.admit_no, tblCRRCensusCrosstab_Temp.off_code, tblCRRCensusCrosstab_Temp.OfficeName, tblCRRCensusCrosstab_Temp.first_name, tblCRRCensusCrosstab_Temp.last_name
FROM tblCRRCensusCrosstab_Temp
ORDER BY Rnd([ID]) DESC;

[Leesha]

That did it. Now to understand why the descending sort made a difference?

Also, is there a way to ensure that other criteria are met. For example, I have three different offices that make up the 25 records in the random sample. Is there a way to run this show office 1 has 15 charts, office 2 has 5 charts and office 3 has 5 charts?

Thanks,

Leesha

[HansV]

You'll have to create three separate queries, one for each office, then combine them in a union query. For example:

Query1:

SELECT TOP 15 tblCRRCensusCrosstab_Temp.ID, tblCRRCensusCrosstab_Temp.client_no, tblCRRCensusCrosstab_Temp.admit_no, tblCRRCensusCrosstab_Temp.off_code, tblCRRCensusCrosstab_Temp.OfficeName, tblCRRCensusCrosstab_Temp.first_name, tblCRRCensusCrosstab_Temp.last_name
FROM tblCRRCensusCrosstab_Temp
WHERE tblCRRCensusCrosstab_Temp.off_code=1
ORDER BY Rnd([ID]) DESC;

Query2:

SELECT TOP 5 tblCRRCensusCrosstab_Temp.ID, tblCRRCensusCrosstab_Temp.client_no, tblCRRCensusCrosstab_Temp.admit_no, tblCRRCensusCrosstab_Temp.off_code, tblCRRCensusCrosstab_Temp.OfficeName, tblCRRCensusCrosstab_Temp.first_name, tblCRRCensusCrosstab_Temp.last_name
FROM tblCRRCensusCrosstab_Temp
WHERE tblCRRCensusCrosstab_Temp.off_code=2
ORDER BY Rnd([ID]) DESC;

Query3:

SELECT TOP 5 tblCRRCensusCrosstab_Temp.ID, tblCRRCensusCrosstab_Temp.client_no, tblCRRCensusCrosstab_Temp.admit_no, tblCRRCensusCrosstab_Temp.off_code, tblCRRCensusCrosstab_Temp.OfficeName, tblCRRCensusCrosstab_Temp.first_name, tblCRRCensusCrosstab_Temp.last_name
FROM tblCRRCensusCrosstab_Temp
WHERE tblCRRCensusCrosstab_Temp.off_code=3
ORDER BY Rnd([ID]) DESC;

Union query:

SELECT * FROM Query1
UNION SELECT * FROM Query2
UNION SELECT * FROM Query3;

[Leesha]

Wow, not only is that awesome but it makes the way I do union queries sooooooooooooo much easier!!!!

Thanks so much!
Leesha