Random Selection by User

[FBxiii]

Hi.

I have got a table with around 20,000 records in. I need to be able to
bring back a 10% random sample of the records 'actioned' by user.

e.g. User A has completed 2,000 records and User B has completed 1,000.

I need the query to show 200 records for user A and 100 records for user B.
Preferably random.

Is this possible?

Thanks,
Steve.

 

[Douglas J. Steele]

Take a look at http://www.mvps.org/access/queries/qry0011.htm at "The Access
Web"

 

[John Spencer]

Yes, it is possible. Random is a bit slower.

The basic query would look like the following.

SELECT *
FROM someTable
WHERE SomeTable.PrimaryKeyField
(SELECT Top 10 PERCENT PrimaryKeyField
FROM SomeTable as Tmp
WHERE tmp.User = SomeTable.User
ORDER BY Rnd(Len(User)), PrimaryKeyField)

One potential problem, this will might return the same set of records each time.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[FBxiii]

Thanks for the link. I have got that piece of code already but it only
brings back the top 100 records, across all users. I need it to be 10% per
user...

 

[Douglas J. Steele]

It's bringing back a 100 records because that's what the sample code says to
do.

You'll have to change what number's after Top in order to have a different
number of records returned.

If your intent is to assign 10% to each user with no overlap, you've got a
bit of an issue. Remember that random numbers are, well, random. Even for a
single user, there's no guarantee that there won't be duplicates in the n
records that are returned. 1, 1, 1 is a perfectly valid result of randomly
selecting 3 values between 1 and 2000.