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
Query to randomly select records
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to randomly select records
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.
- 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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Query to randomly select records
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
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to randomly select records
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;
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;
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Query to randomly select records
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
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to randomly select records
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;
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;
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Query to randomly select records
Wow, not only is that awesome but it makes the way I do union queries sooooooooooooo much easier!!!!
Thanks so much!
Leesha
Thanks so much!
Leesha