Randomly select a % of records run from a query

[Groundrush]

Does anyone know how to randomly select a % of records returned in a query?

If there are 100 records I only want 10% to be returned & all random.

I don't want to have to manually do this each time & I'm hoping there is a way to do this in Access.

Each record will have a unique field that the query will be based on each time I run it called JobNo.


All I need I guess is 10% of jobno's to be returned

Any help will be greatly appreciated

Thanks

 

[CraigDolphin]

How about creating a query of the data that includes a calculated field which generates a random number between 0 and 100 (lookup the Rnd function). Then use a criterion of <=10 on that field.

On average, this should return 10% of the rows. However, each time you run it the actual % might vary slightly due to random chance.

Or, another way would be to count the total number of rows and set a variable to match the required number of rows you want returned. Create the same random number calculated frield I mentioned before, then use this field to sort your rows (either ascending or descending) Then use the Top <variable name> SQL syntax to return the desired amount of rows.

Because the numbers are randomly generated the specific rows returned by this procedure would change each time you run the query.

HTH

 

[raskew]

Hi -

Give this a try. It was developed to randomly select lottery numbers, but should do the job for you.

Public Function RandLotto2(Bottom As Integer, Top As Integer, _
                          Amount As Integer) As String
                    
'*******************************************
'Purpose:   Produce x random/unique/sorted numbers
'              between bottom and top.
'Sources:  http://www.ozgrid.com/VBA/RandomNumbers.htm
'             http://www.tek-tips.com/viewthread.cfm?qid=756905 (sort routine - Roy Vidar's post)
'Inputs:    ? RandLotto2(1, 55, 5)
'Output:    1 5 11 18 44  (5 unique, random, sorted
'           numbers between 1 and 55)
'*******************************************

    Dim iArr As Variant
    Dim i As Integer
    Dim j As Integer
    Dim r As Integer
    Dim temp As Integer
    
    ReDim iArr(Bottom To Top)
    For i = Bottom To Top
        iArr(i) = i
    Next i
    
    For i = Top To Bottom + 1 Step -1
        Randomize
        r = Int(Rnd() * (i - Bottom + 1)) + Bottom
        temp = iArr(r)
        iArr(r) = iArr(i)
        iArr(i) = temp
    Next i
    
    For i = Bottom To Amount
        For j = i + 1 To Amount
            If iArr(i) > iArr(j) Then
                temp = iArr(i)
                iArr(i) = iArr(j)
                iArr(j) = temp
            End If
        Next j
    Next i
    
    For i = Bottom To Bottom + Amount - 1
        RandLotto2 = RandLotto2 & " " & iArr(i)
    Next i
    
    RandLotto2 = Trim(RandLotto2)
    
End Function

HTH - Bob

 

[vbaInet]

I was thinking along the same lines as CraigDolphin. The TOP predicate also does percentages too.

SELECT TOP 10 PERECENT ... etc

It's a good start but in the future the algorithm would require further fine-tuning like ensuring the last random 10% were not included in the next 10% to be generated or the next 5 consecutive 10% of records.

NB: I think there's also an example in the Code Sample section of this forum for generating random numbers. Check it out too.

 

[Groundrush]

Thanks everyone, will have a look & let you know how I get on

 

[raskew]

Hi -

Trying copying the function at Post #3 to a standard module then, from the debug window, run the following 10 times. Here's an example:

 [B]? RandLotto2(1, 100, 10)[/B]
 1 11 14 16 47 56 68 70 79 98
20 24 29 32 42 47 67 73 87 89
 7   8 38 39 53 61 65 75 92 97
14 15 24 28 32 43 56 66 81 96
10 20 28 45 66 71 76 96 97 100
 6   9 15 28 35 37 63 69 73 81
 2 13 24 26 29 47 52 53 62 63
14 47 51 52 56 60 66 78 88 96
13 38 40 51 56 59 61 79 90 94
18 21 32 37 48 52 69 78 80 85

Bob

 

[Groundrush]

Hi -

Trying copying the function at Post #3 to a standard module then, from the debug window, run the following 10 times. Here's an example:

 [B]? RandLotto2(1, 100, 10)[/B]
 1 11 14 16 47 56 68 70 79 98
20 24 29 32 42 47 67 73 87 89
 7   8 38 39 53 61 65 75 92 97
14 15 24 28 32 43 56 66 81 96
10 20 28 45 66 71 76 96 97 100
 6   9 15 28 35 37 63 69 73 81
 2 13 24 26 29 47 52 53 62 63
14 47 51 52 56 60 66 78 88 96
13 38 40 51 56 59 61 79 90 94
18 21 32 37 48 52 69 78 80 85

Bob

Not sure how I could use this to select a percentage from a preselected list already produced in a query

the job no's i'm refering to will start from 20000 so far it's now up to 23920 & counting.

the idea is every month I will generate a report for all completed jobs on the system then run another query that hopefully selects 10% from the previous results.

I am looking into the Rnd function to see if that will work

 

[Groundrush]

How about creating a query of the data that includes a calculated field which generates a random number between 0 and 100 (lookup the Rnd function). Then use a criterion of <=10 on that field.

On average, this should return 10% of the rows. However, each time you run it the actual % might vary slightly due to random chance.

Or, another way would be to count the total number of rows and set a variable to match the required number of rows you want returned. Create the same random number calculated frield I mentioned before, then use this field to sort your rows (either ascending or descending) Then use the Top <variable name> SQL syntax to return the desired amount of rows.

Because the numbers are randomly generated the specific rows returned by this procedure would change each time you run the query.

HTH

I've got so far as to create a qry that captures the data required for the random 10% selection

Then created another query based on the first that selects one record from it using this code

SELECT TOP 1 *
FROM [SELECT TOP 100 * FROM qryPostInspectionLesYates WHERE qryPostInspectionLesYates.Status = "Invoiced"]. AS qryPostInspectionLesYates
ORDER BY Rnd([qryPostInspectionLesYates].[JobNo]);

Also tried

SELECT TOP 1 *
FROM [SELECT TOP 1000000 * FROM qryPostInspectionLesYates WHERE qryPostInspectionLesYates.Status = "Invoiced"]. AS qryPostInspectionLesYates
ORDER BY Rnd([qryPostInspectionLesYates].[JobNo]);

That's the closest I've got to so far

How do I move on from here & get it to select 10% from the query rather than just the one?


Thanks

 

[raskew]

I'm confused.

If there are 100 records I only want 10% to be returned & all random.
....
All I need I guess is 10% of jobno's to be returned

You're jumping around. First we have what you initially posted (see above) and then we're talking the top 10% (which obviously won't be all random). Which is it?

Bob

 

[namliam]

As suggested by a previous poster try using the PERCENT ...

SELECT TOP 10 [B][U]PERCENT[/U][/B] *
FROM [SELECT TOP 100 * FROM qryPostInspectionLesYates WHERE qryPostInspectionLesYates.Status = "Invoiced"]. AS qryPostInspectionLesYates
ORDER BY Rnd([qryPostInspectionLesYates].[JobNo]);

 

[Groundrush]

I'm confused.



You're jumping around. First we have what you initially posted (see above) and then we're talking the top 10% (which obviously won't be all random). Which is it?

Bob

Sorry, difficult to explain properly. All I wanted was to be able to return a % of records from an existing qry, in this case it will be 10%.

 

[Groundrush]

As suggested by a previous poster try using the PERCENT ...

SELECT TOP 10 [B][U]PERCENT[/U][/B] *
FROM [SELECT TOP 100 * FROM qryPostInspectionLesYates WHERE qryPostInspectionLesYates.Status = "Invoiced"]. AS qryPostInspectionLesYates
ORDER BY Rnd([qryPostInspectionLesYates].[JobNo]);

Thanks Namlin

Works great

 

[raskew]

Is it random 10% or top 10%?

Come on - you can do it!

Bob

 

[Groundrush]

Is it random 10% or top 10%?

Come on - you can do it!

Bob

I think I'm almost there

I need random 10% of records based on the results of a query

testing it using this

SELECT TOP 10 PERCENT *
FROM [SELECT TOP 100 * FROM qryPostInspectionLesYates WHERE qryPostInspectionLesYates.Status = "Invoiced"]. AS qryPostInspectionLesYates
ORDER BY Rnd([qryPostInspectionLesYates].[JobNo]);

that is based on 165 records gives me 10 random records instead of the expected 16.5 or 17 (not sure how to get half a record... I'm assuming it would round up.

 

[Groundrush]

TOP n [PERCENT] Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

If you do not include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.

The TOP predicate does not choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.

You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose that, instead of the top 25 students, you want the bottom 10 percent of the class:

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

The ASC predicate specifies a return of bottom values. The value that follows TOP must be an unsigned Integer .

TOP does not affect whether or not the query is updatable.

I can't find any help files that does what I need

I don't want the top 10 or the bottom 10 just 10% of records based on the result of another query.

The results from the first query could return different amount of records each time it's run

100 records should return 10
200 records should return 20 etc...

 

[G81]

I think I'm almost there

I need random 10% of records based on the results of a query

testing it using this

SELECT TOP 10 PERCENT *
FROM [SELECT TOP 100 * FROM qryPostInspectionLesYates WHERE qryPostInspectionLesYates.Status = "Invoiced"]. AS qryPostInspectionLesYates
ORDER BY Rnd([qryPostInspectionLesYates].[JobNo]);

that is based on 165 records gives me 10 random records instead of the expected 16.5 or 17 (not sure how to get half a record... I'm assuming it would round up.

Thats giving you 10 records because you are selecting 10% of TOP 100 which = 10. If you take the top 100 out it should return 10% of everything in your qryPostInspectionLesYates

 

[Groundrush]

Thats giving you 10 records because you are selecting 10% of TOP 100 which = 10. If you take the top 100 out it should return 10% of everything in your qryPostInspectionLesYates

Actually instead of removing it i changed it too TOP 100 PERCENT & I seem to get expected results this time

Thanks

 

[namliam]

I made a quick little table which contains 60 records
Select TOP 10 returns me 10 records (obviously)
Select TOP 10 PERCENT returns me 6 (seems right)
Select TOP 11 PERCENT returns me 7 (seems OK too)

And because I order by RND(SoemField) I get a random 10 on top 10 percent, seems to work just fine for me

I think I'm almost there

I need random 10% of records based on the results of a query

testing it using this

SELECT TOP 10 PERCENT *
FROM [SELECT TOP 100 * FROM qryPostInspectionLesYates WHERE qryPostInspectionLesYates.Status = "Invoiced"]. AS qryPostInspectionLesYates
ORDER BY Rnd([qryPostInspectionLesYates].[JobNo]);

that is based on 165 records gives me 10 random records instead of the expected 16.5 or 17 (not sure how to get half a record... I'm assuming it would round up.

It seems logical you would get 10 instead of the "expected" 16.5.
Your expectation is wrong
The subselect has a TOP 100, which limits your subquery to 100 records of which 10% is .... 10.

Remove the TOP 100 and you should be golden

Edit, seems like I had this sitting on my desktop to long sorry

 

[Groundrush]

I made a quick little table which contains 60 records
Select TOP 10 returns me 10 records (obviously)
Select TOP 10 PERCENT returns me 6 (seems right)
Select TOP 11 PERCENT returns me 7 (seems OK too)

And because I order by RND(SoemField) I get a random 10 on top 10 percent, seems to work just fine for me



It seems logical you would get 10 instead of the "expected" 16.5.
Your expectation is wrong
The subselect has a TOP 100, which limits your subquery to 100 records of which 10% is .... 10.

Remove the TOP 100 and you should be golden

Edit, seems like I had this sitting on my desktop to long sorry

I did try to remove the TOP 100 but but had errors that I could not work out what I was doing wrong. I did however discover that by changing it to SELECT TOP 100 PERCENT it appeared to work

I tested it on 165 records which is 16.5% I returned 17 records
then 29 records which is 2.9% returning 3 records
then finally 77 records 7.7% returning 8 records

I am happy with this unless you think this is incorrect

 

[namliam]

I tested it on 165 records which is 16.5% I returned 17 records
then 29 records which is 2.9% returning 3 records
then finally 77 records 7.7% returning 8 records

I am happy with this unless you think this is incorrect

This is GROSSLY incorrect

LOL

I understand your typo though, so all is good, but BOY is that wrong