Using MS Access/Random Select Query

Advertisement


Question
Random Select Query
Random Select Query  
QUESTION: Hi Scottgem,

I'm trying to build a query where I can randomly select 5 of each unique values in a UserID field. For example, I'll have userID "Jim" 15 times on a table and I just need the query to  randomly choose 5.  If "Jim" only appears less than 5, then I'd like to see all.  And it would choose the next UserID and provide another random 5 so on until it no longer has anymore unique UserIDs.  I've attached a screenshot of my table to give you an idea.  Thank you.

ANSWER: The problem here is the Random selection. You can easily get a TOP N of group query to get 5 of each User. See this article: http://support.microsoft.com/kb/210039

But to randomly select 5 is much more complex. What you will need to do is nested loop through a recordset. First create a recordset of just the RecordID and UserID, Sorted by UserID and RecordID. Then loop through this recordset writing the RecordIDs to an Array. When the UserID changes, run another loop that generates a random number within the range of the array, then checks to see if there is a match within the array. You do this until 5 records are selected. (if there are 5 or less in the array, just skip the inner loop).

Whenever you find a match write the RecordID to a temp table. Finally create a query, joining your main table with the temp table on ID.  

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thanks Scott.  My VBA is super rusty so using the link you provided along with some internet searches, I came with the following in SQL and in theory it should work, but I can't seem to get it to work!  I know I'm very close!!

SELECT [PPA Data].*
FROM [PPA Data]
WHERE ((In (SELECT Top 5 [UserID] FROM [PPA Data]
Order By
Getrandom()*([ID]) desc));

The Getrandom() function looks like this.

Option Compare Database
Dim RandNum As Long
Function GetRandom()
If RandNum > 100 Then
       RandNum = 0
   Else
       RandNum = RandNum + (Int((100 - 1 + 1) * Rnd + 1))
   End If
   GetRandom = RandNum
End Function

Any help would be much appreciated!!!

ANSWER: What seems to be happening? Can you get the query to work with just getting the Top 5?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA


---------- FOLLOW-UP ----------

QUESTION: Secott,

I should have done a better job of troubleshooting.  Answer is no, I can't even get the top 3 or 5 for that matter to work.  

Using this below just gives me all sets of "Jim" and "Joe" but for some reason no "Lynn".

SELECT [PPA Data].*
FROM [PPA Data]
WHERE (((UserID) In (SELECT Top 3 UserID FROM [PPA Data])));

However, using this code, I get all of "Lynn" and no "Joe" or "Jim"

SELECT [PPA Data].*
FROM [PPA Data]
WHERE (((UserID) In (SELECT Top 3 UserID FROM [PPA Data]
WHERE UserID=[PPA Data].UserID
Order By
GetRandom()*([ID]) Desc)));

I'm spending all my time but I don't know what else to do.  I'm playing with the SQL and I'm not getting anywhere.  I wish I could just send you the Access File to look at.  I'm sure it is just matter few syntax here and there.  Anything you got, I'm all ears.

Answer
You post a question on askmehelpdesk.com where you can upload a zipped copy of the database.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.