sql random query 1

[rushie84]

I am trying to make a query to return a random 5% of the names in an Access database. does anyone have any suggestions?

 

[dodge20]

Here is an example of a random function

'************ 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.

Hope this helps


Dodge20

 

[dodge20]

whoops, count * 5% isn't going to work. I will try to find the solution for you.

Dodge20

 

[mikevh]

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".

 

[MDLynch]

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

 

[JonFer]

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.

 

[dhookom]

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.

 

[JonFer]

Duane,

I didn't realize Access had a random function. Guess I never needed it before. Thanks for the tip.

As for my post, well...never mind!