'************ Code Begin ***********
'Code courtesy of
'Joe Foster
Function Randomizer () As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize : AlreadyDone = True
Randomizer = 0
End Function
'************ Code End *************
Now to get 100 questions picked at random:
select top 100 mytable.*from mytable
where randomizer() = 0
order by rnd(isnull(mytable.question) * 0 + 1)
now to get the 5%, I would use something like count * .05 instead of top 100.
After you set up the tables and fields in your query,
right-click the gray area in the query design window
where the tables are. Click "Properties" and choose "5%" from the drop-down next to "Top Values".
Hello. I am trying to select a random 5% sample of each individual person's work from a list of claims processed. I'll try to describe my table to you: Each row in the table displays an entire claim record. The table is an import of all the claims processed on a given day. One field in the table is called UserID and displays the individual processor's system login name. In a 1000 row table, there might be 5 or 10 different User Id's depending on how many individuals processed claims that day. I want to select a random 5% sample from each UserID. Can anyone help? Thank You. Michelle
This is not a fully automated method and uses Excel but it will work.
First, get the table sorted internally by UserID and whatever else you want. You can either use a Make-Table query with the correct sort or set a primary index which has UserID as the first field and then Compact the database.
Then add an autonumber field to the table. Write a query which gives you the min(autonumber) and total records per UserID.
In the first row of an Excel file, enter the UserID (A1), TotalRcds (B1), and FirstAutoNum (C1). Write a formula in cell B2 to generate the correct range of random numbers for the User ID:
=INT(B$1*RAND())+C$1
Drag this formula down for as many rows as you need. In column A put the formula =A$1 and drag that down. You can repeat this in another set of columns for as many users as you want to do at a time. You might want to set the worksheet to Manual Calculation so the random numbers only change when you hit F9 (recalc).
In Access, create a two-field table with UserID and a long integer field called RecordID. Paste/Append your Excel data to this table, one UserID at a time. Join this table to the claims table. The Excel file and new Access table can be re-used for other tables.
This can all be done without code or other. A typical query for a client table that has an autonumber primary key of cliCliID:
SELECT TOP 5 PERCENT Rnd([cliCliID]) AS Expr1, tblClients.*
FROM tblClients
ORDER BY Rnd([cliCliID]);
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.