Random Sampling

[ConfusedNAccess]

Need help the module below works great for producing random records, however I have 7 users that I will need to match for, meaning.. i need top 3 random records for 5 different groups-i saw group because i read somewhere that i need to use a group statement.. so maybe group isn't a good term.. The LSO field is a text field that contains names for 6 different people. I need random records set by top 3 for each of those people. right now i'm just getting random data.. and not random for each of the LSO names.. also.. it would be wonderful if the top 3 could be replaced by a parameter, so who ever is using the form can change the top n number when prompted when the query is run....so they can select however many they want.. ideas.. thoughts. someone please?!?!?

please someone help!!!!

Sub PickRandomR()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim strSQL As String
Dim strTableName As String

' 1: Create a new temporary table containing the required fields
'

strSQL = "SELECT lastcalcs.lso, lastcalcs.am, lastcalcs.borname, lastcalcs.lastcalcdate, lastcalcs.lastcalctype " & _
"INTO tblTemp " & _
"FROM lastcalcs;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 2: Add a new field to the new table
Set db = CurrentDb()
Set tdf = db.TableDefs("tblTemp")
Set fld = tdf.CreateField("RandomNumber", dbSingle)
tdf.Fields.Append fld

' 3: Place a random number in the new field for each record
Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
rst.MoveFirst
Do
Randomize
rst.Edit
rst![RandomNumber] = Rnd()
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing

' 4: Sort the data by the random number and move the top 3 into a new table
strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
strSQL = "SELECT TOP 3 SELECT lastcalcs.lso, lastcalcs.am, lastcalcs.borname, lastcalcs.lastcalcdate, lastcalcs.lastcalctype " & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.randomnumber;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 5: Delete the temporary table
db.TableDefs.Delete ("tblTemp")
End Sub

 

[Zion7]

Something like...

strSQL = "SELECT TOP " & Me.txtTop & " SELECT lastcalcs.lso, lastcalcs.am, lastcalcs.borname, lastcalcs.lastcalcdate, lastcalcs.lastcalctype " & _
"INTO " & strTableName & _
" FROM tblTemp " & _
" WHERE txtEditBy ='" & CurrentUser() & _
"' ORDER BY tblTemp.randomnumber;"

Me.txtTop is a new, unbound field on your form
for User to select number og Top records.
I made up "txtEditBy', ...what is there to determine
who's records are who's?

 

[dwight1]

I was looking for the same help on MS Access database. I am trying to do audits on the auditors. I have seven different people who have completed X number of audits. I want to pick top 10 or any number depending on audit completion.

I have used [auditor name]and also [month] for picking up the records through a query. The problem is how to pass the top X number of records.

Any help is appreciated.

Dwight

 

[Zion7]

Dwight, of that same query (the last, one you spoke of),
Do a Select Top on it...


SELECT TOP 10 pkID, txtName FROM qryAuditName....