select random records

[mlpearce2]

I have a table that contains data for 3 hospitals and could contain up to 30 hospitals.

I need help in creating something that would grab 5 random records for the following ranges of charges ($) for a specific hospital chosen from a combo box:
1. 0-10k
2. 10-25k
3. 25-50k
4. 50-100k
5. 100k+

Here are the fields in my table:
Facility
Referral
Name
Account #
MR Number
Status
I/O/E
Admission
Discharge
Return Date
Charges
Action Summary
Insurance Plan
Financial Class
ID
IsExported
BalBand

ID is an autonumber field that i was planning to use as a base for random numbers. BalBand is an identifier for each account based on charges. Here is how it is assigned:

1= Charges between 0 and 10,000
2= Charges between 10,001 and 25,000
3= Charges between 25,001 and 50,000
4= Charges between 50,001 and 100,000
5= Charges > 100,000

So basically I would need a random 5 from each of those identifiers (BalBand). Any thoughts?

Thanks in advance

[orange]

Take a look here
http://www.databasedev.co.uk/random_query.html

[mlpearce2]

Take a look here
http://www.databasedev.co.uk/random_query.html

I actually found that in my own searching. It seems like it does what I would need. But in order to implement that wouldn't I need to create a query in access itself, not vba, for each range of charges and each facility in the table? Or is there a simpler way to do that?

I have created the following code:

lowerbound = DMin("ID", "qa_data", "balband = " & balband & " AND Facility ='" & fac & "'")
upperbound = DMax("ID", "qa_data", "balband = " & balband & " AND Facility ='" & fac & "'")

If DCount("ID", "qa_data", "Balband = " & balband & " and facility = '" & fac & "'") <= 5 Then
sql = "UPDATE qa_data SET qa_data.IsExported = Yes where qa_data.status <> '" & status & "' and qa_data.balband = " & balband & " and qa_data.facility = '" & fac & "';"
CurrentDb.Execute sql
Exit Sub
End If

Do While i <> 5
rndnum = Int((upperbound - lowerbound + 1) * rnd + lowerbound)
If DLookup("balband", "qa_data", "ID = " & rndnum) = balband Then
If DLookup("status", "qa_data", "ID= " & rndnum) <> status Then
i = i + 1
sql2 = "UPDATE qa_data SET qa_data.IsExported = Yes where ID = " & rndnum & ";"
CurrentDb.Execute sql2
End If
End If
Loop

DoCmd.RunSQL "insert into exp_tbl select qa_data.* from qa_data where qa_data.isexported = yes"


this code seems to work for the most part, but its a little off. basically the loop should keep going until i=5, meaning that it has found 5 accounts for each range of charges.

Any suggestions?

[orange]

this code seems to work for the most part, but its a little off. basically the loop should keep going until i=5, meaning that it has found 5 accounts for each range of charges.

Any suggestions?

What exactly does a little off mean?

Here's code I use to find a RandomNumber in a range of Lo - HI



'---------------------------------------------------------------------------------------
' Procedure : randomNumber
' Author : Jack
' Created : 11/18/2010
' Purpose : To Generate Random numbers between and including a range of numbers.
'Lo and Hi are the lowest and highest random numbers you wish to generate.
'The Randomize keyword is critical to getting different results for each Access session.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function randomNumber(Lo As Integer, Hi As Integer) As Integer
On Error GoTo random_Error
Randomize
randomNumber = Int((Hi - Lo + 1) * Rnd - Lo)

On Error GoTo 0
Exit Function

random_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure randomNumber of Module jackAccessMonster"
End Function

You can test it with some samples and tailor it as you wish.

[mlpearce2]

What exactly does a little off mean?

the code should flag 25 accounts total:

5 should be from balband 1 (charges between 0 and 10,000)
5 should be from balband 2 (charges between 10,001 and 25,000)
5 should be from balband 3 (charges between 25,001 and 50,000)
5 should be from balband 4 (charges between 50,001 and 100,000)
5 should be from balband 5 (charges greater than 100,000)

in a test case i ran only 9 accounts were flagged. it didnt flag anything from balbands 3 or 4, when there is valid data for those. The table did not contain data for Balband 5. So I am thinking that the random number that the code generated did not match an ID in the table, but the loop still carried on. The counter should have only incremented if it found a valid match.

[mlpearce2]

so it looks like the sql statement inside the loop is flagging the right number of accounts, but the last sql statement at the end of the sub routine isn't appending all of the flagged records to the exp_tbl.

I can run the query in access itself and it works fine.

any ideas?

[mlpearce2]

how can i create a function to be used in a query that would assign a truly random number to each record in the table?

[stanl]

how can i create a function to be used in a query that would assign a truly random number to each record in the table?

as there is no such thing as a truly random number generator (for PC's at least) - why not just use admission data, the assumption being people are admitted randomly not in some deterministic order. You could pick a semi-random startdate, then query to select TOP n WHERE admissiondate>= stardate AND [other criteria] ORDER by admissiondate ASC...

I used this logic years ago to analyze admissions in selected universities... just .02 Stan