How do I select random rows by a specific column using Rnd() function?

[srhee]

I've been asked to create a sample of 50% from a dataset weighted by a category. For example, in the following dataset, I would need to randomly pick 3 EE IDs in Payband A, 1 in Payband B, and 5 in Payband C. I know how to randomly pick 50% of the entire dataset using this SQL: SELECT TOP 50 PERCENT * from myTable ORDER BY rnd(EE ID), but I don't know how to make the query run the 50% sample by the field Payband. I could run the above SQL for each payband, but I have 62 categories, and I really don't want to do that. I'm an HR person and not a developer/programmer, so any help would be appreciated. Thanks!

Payband	EE ID
A	12345
A	12346
A	12347
A	12348
A	12349
A	12350
A	12351
B	12352
B	12353
C	12354
C	12355
C	12356
C	12357
C	12358
C	12359
C	12360
C	12361
C	12362
C	12363

[rpeare]

What are you doing with this data once you have made your selection? The limitation of the TOP is that it really only works the way you want it to for one category not multiple categories. You could theoretically run your random TOP for each category you have and append those results to a temporary table, in essence it would give you what you want but it would involve VBA.

The other method is to create a UNION query, I don't know if you could manage to get 62 paybands included in it though but it would be something like

SELECT TOP 50 PERCENT myTable.PayBand, *
FROM myTable
WHERE (((myTable.PayBand)="A"))
ORDER BY Rnd(EEID)
UNION ALL
SELECT TOP 50 PERCENT myTable.PayBand, *
FROM myTable
WHERE (((myTable.PayBand)="B"))
ORDER BY Rnd(EEID)
UNION ALL
SELECT TOP 50 PERCENT myTable.PayBand, *
FROM myTable
WHERE (((myTable.PayBand)="C"))
ORDER BY Rnd(EEID)
UNION ALL
etc...

[rpeare]

p.s. not sure the top 50% thing will work with union queries.

[srhee]

The data will be used to identify which employees will be included in a survey. I was hoping to avoid having to run the query 62 times. If there is VBA code that would automatically look for unique paybands and run the rnd() function for each of those categories and append to a table, that would be ideal. I would only need EE ID to be output to a new table. Thank you!

[rpeare]

let's just say you create a table

tblSurveyEmployees
with the field EE_ID (I'm going to use an underscore where you have a space because I don't use spaces in any database object, it causes a lot of problems because I code a lot)

Now let's assume that the dataset you've shown is on a table tblEmployees

your code would be something like:

Dim db As Database
Dim rstPayBand As Recordset
Dim sSQL As String

Set db = CurrentDb

db.Execute ("DELETE * FROM tblSurveyEmployees")
Set rstPayBand = db.OpenRecordset("SELECT PayBand FROM tblEmployees GROUP BY PayBand ORDER BY PayBand")

Do While rstPayBand.EOF <> True
    sSQL = "INSERT INTO tblSurveyEmployees (EE_ID) "
    sSQL = sSQL & "SELECT TOP 50 PERCENT EE_ID "
    sSQL = sSQL & "FROM tblEmployees "
    sSQL = sSQL & "WHERE ((PayBand) = '" & rstPayBand.Fields("Payband") & "') "
    sSQL = sSQL & "ORDER BY rnd([EE_ID])"
    db.Execute sSQL
    rstPayBand.MoveNext
Loop
rstPayBand.Close
Set db = Nothing

[orange]

I mocked up your data and used a technique that was discussed in a different forum for Selecting Top N from a Group.

Take you original table and add a field with Number datatype to hold a Random number
I added a field xtrafield to the Table Payband.

Then, Populate the xtrafield with a Random number

'---------------------------------------------------------------------------------------
' Procedure : PayBandStuff
' Author    : Jack
' Date      : 20/02/2014
' Purpose   : To put a random number in a field (xtrafield) in a table
'
'This is a technique when dealing with random TOP N records per Group
'
'In the sql you Select the Top N or Percent
' Group By the grouping field and
' Order By The Random field   (xtrafield in this example)
'---------------------------------------------------------------------------------------
'
Sub PayBandStuff()
      'populate xtrafield with random number
          Dim sql As String
          Dim rs As DAO.Recordset
          Dim i As Integer
10       On Error GoTo PayBandStuff_Error

20        Randomize 'must use this to getnew random numbers
30        Set rs = CurrentDb.OpenRecordset("payBand")
40        Do While Not rs.EOF
50            rs.Edit
60            rs!xtrafield = Rnd() * 300 'just a number
70            rs.Update
80            rs.MoveNext
90        Loop

100      On Error GoTo 0
110      Exit Sub

PayBandStuff_Error:

120       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure PayBandStuff of Module AWF_Related"
End Sub

So now my table values are:

Payband
EEID
XtraField

A
12345
267.7998046875

A
12346
99.4330291748047

A
12347
32.2006149291992

A
12348
282.146148681641

A
12349
193.814682006836

A
12350
255.543670654297

A
12351
136.402862548828

B
12352
126.936866760254

B
12353
262.250152587891

C
12354
116.814460754395

C
12355
127.859062194824

C
12356
1.76260471343994

C
12357
265.189910888672

C
12358
100.744163513184

C
12359
272.822875976563

C
12360
251.612380981445

C
12361
236.416961669922

C
12362
199.733917236328

C
12363
12.8409328460693

Then use a select query to get the TOP 50 Percent of each Payband Grouped By Payband and Ordered By the RandomField

SELECT TOP 50 PERCENT Payband.payband, Payband.EEID, Payband.XtraField
FROM Payband
GROUP BY Payband.payband, Payband.EEID, Payband.XtraField
ORDER BY  Payband.XtraField

Then select only the fields you want in the order you want them.

SELECT payband, EEID
FROM (SELECT TOP 50 PERCENT Payband.payband, Payband.EEID, Payband.XtraField
FROM Payband
GROUP BY Payband.payband, Payband.EEID, Payband.XtraField
ORDER BY  Payband.XtraField)  AS [%$##@_Alias]
ORDER BY Payband;

Giving this result:

payband
EEID

A
12349

A
12351

A
12346

A
12347

B
12352

C
12355

C
12354

C
12358

C
12363

C
12356

Nice technique, right.
Good luck with your project.

[srhee]

Thanks so much! I was able to use the UNION query after all. I just had to create two to split out the code. I can't figure out how to use VBA, so that was quicker for me. I was able to create a module w/ the code and created a form w/ a button to run the event procedure, but I got the following error:
Compile error: Invalid Outside Procedure

And the debugger highlights the word "Set" in the line of code Set db = CurrentDb

I wasn't sure if I was supposed to change the name of the db to my own db name.

No need to respond unless it's quick and you have time. Your first response was very helpful and got the job done!

[rpeare]

You'd have to have the correct reference to use it.

In your code window click on TOOLS > REFERENCES and see what you have currently loaded.

These are the ones I have loaded by default

Visual Basic for Applications
Microsoft Access xx.x Object library (where xx.x will be whatever your version of access is)
Microsoft Visual Basic for applications Extensibility x.xx
Microsoft Office xx.x Access database engine objects