Random Record Selection

[Guest]

Here is my scenario:
I have a table [Open Cases] that consists of field [Team], field [RN], field
[Case]. There are 6 Teams, 56 RN's (each assigned to a Team) and 3312 Cases.
What I need to do is randomly select 3 Cases for each RN.

I have been reading all the different posts on Random Records and tried what
has been suggested (still cannot get the Randomize to work), but I need to do
more than randomly select a certain # of records; I need a certain # of
records per RN.

I am using ACCESS 97.
Any Suggestions?

 

[Allen Browne]

The following example shows how you could select 3 random cases for each RN,
and assign them to a table named WorkToDo.

It does this by opening a recordset of each RN, looping through them, and
then selecting random cases for each one.

Note that this is an example only; it does not avoid assigning multiple RNs
to the same Open Case.

To get the Append query statment to work correctly with your data structure,
mock up a query in query design view, drop any literal values into the
Criteria row, change it to an Append (Append on Query menu), and then switch
it to SQL View (View menu) to see an example of what you need.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql as String

Randomize
Set db = CurrentDb()
strSql = "SELECT RnID FROM RN;"
Set rs = db.OpenRecordset(strSql)

Do While Not rs.EOF
strSql = "INSERT INTO WorkToDo (CaseID, RnID, WorkDate) SELECT TOP 3
CaseID, " & rs!RnID & " AS RnID, Date() AS WorkDate FROM [Open Cases] ORDER
BY Rnd([Open Cases].ID), [Open Cases].ID;"
db.Execute strSql, dbFailOnError
rs.MoveNext
Loop

set rs = Nothing
Set db = Nothing

 

[Guest]

The [Case] is already "assigned/associated" with one of the 56 RN's so I am
not using this to assign. I want to pull 3 cases for each RN for an audit.
Do you have any different suggestions knowing this additional information?

The following example shows how you could select 3 random cases for each RN,
and assign them to a table named WorkToDo.

It does this by opening a recordset of each RN, looping through them, and
then selecting random cases for each one.

Note that this is an example only; it does not avoid assigning multiple RNs
to the same Open Case.

To get the Append query statment to work correctly with your data structure,
mock up a query in query design view, drop any literal values into the
Criteria row, change it to an Append (Append on Query menu), and then switch
it to SQL View (View menu) to see an example of what you need.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql as String

Randomize
Set db = CurrentDb()
strSql = "SELECT RnID FROM RN;"
Set rs = db.OpenRecordset(strSql)

Do While Not rs.EOF
strSql = "INSERT INTO WorkToDo (CaseID, RnID, WorkDate) SELECT TOP 3
CaseID, " & rs!RnID & " AS RnID, Date() AS WorkDate FROM [Open Cases] ORDER
BY Rnd([Open Cases].ID), [Open Cases].ID;"
db.Execute strSql, dbFailOnError
rs.MoveNext
Loop

set rs = Nothing
Set db = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is my scenario:
I have a table [Open Cases] that consists of field [Team], field [RN],
field
[Case]. There are 6 Teams, 56 RN's (each assigned to a Team) and 3312
Cases.
What I need to do is randomly select 3 Cases for each RN.

I have been reading all the different posts on Random Records and tried
what
has been suggested (still cannot get the Randomize to work), but I need to
do
more than randomly select a certain # of records; I need a certain # of
records per RN.

I am using ACCESS 97.
Any Suggestions?

 

[Allen Browne]

You need somewhere to "put" the selected records.
A temp table would be suitable.

For an alternative suggestion, see:
How to Create a "Top N Values Per Group" Query
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The [Case] is already "assigned/associated" with one of the 56 RN's so I
am
not using this to assign. I want to pull 3 cases for each RN for an
audit.
Do you have any different suggestions knowing this additional information?

The following example shows how you could select 3 random cases for each
RN,
and assign them to a table named WorkToDo.

It does this by opening a recordset of each RN, looping through them, and
then selecting random cases for each one.

Note that this is an example only; it does not avoid assigning multiple
RNs
to the same Open Case.

To get the Append query statment to work correctly with your data
structure,
mock up a query in query design view, drop any literal values into the
Criteria row, change it to an Append (Append on Query menu), and then
switch
it to SQL View (View menu) to see an example of what you need.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql as String

Randomize
Set db = CurrentDb()
strSql = "SELECT RnID FROM RN;"
Set rs = db.OpenRecordset(strSql)

Do While Not rs.EOF
strSql = "INSERT INTO WorkToDo (CaseID, RnID, WorkDate) SELECT
TOP 3
CaseID, " & rs!RnID & " AS RnID, Date() AS WorkDate FROM [Open Cases]
ORDER
BY Rnd([Open Cases].ID), [Open Cases].ID;"
db.Execute strSql, dbFailOnError
rs.MoveNext
Loop

set rs = Nothing
Set db = Nothing

Here is my scenario:
I have a table [Open Cases] that consists of field [Team], field [RN],
field
[Case]. There are 6 Teams, 56 RN's (each assigned to a Team) and 3312
Cases.
What I need to do is randomly select 3 Cases for each RN.

I have been reading all the different posts on Random Records and tried
what
has been suggested (still cannot get the Randomize to work), but I need
to
do
more than randomly select a certain # of records; I need a certain # of
records per RN.

I am using ACCESS 97.
Any Suggestions?