select every 79th records

[ddtpmyra]

I have 400K records on my Access database. Now I wanted you pull out randomly records that falls on 79th records from top to bottom. How can you do that on sql?

Your help is much appreciated.
thanks,
DM

[Stewart Ross]

SQL is a language which implements manipulation of relational database tables. These tables are based on set arithmetic, where there is no intrinsic concept of record position. It is not possible to select the Nth record in a sequence in SQL - and if it was, it would be a sequential selection, not a random one.

If you want to select a random sample of your 400k records you will need to assign a random number to each using a query. You can't just use the Rnd function in the query, as it would be evaluated just the once and return the same value for every row. But with a little ingenuity you can call a function, supplying the value of one of your table fields as an argument to the function to force re-evaluation of the random value. An example of such a function is listed below.

Public Function fGenerateRandomNumber(somefield As Variant) As Single
 
'This function generates a random number using the rnd function.
'The value of somefield passed to the function is not otherwise used -
'it simply forces the random number to be generated on every call to
'the function, whereas if Rnd is called in a query
'it is only evaluated once.
'
    fGenerateRandomNumber = Rnd()
End Function

I have tested this on an ordered sequence of numbers from 1 to 4000. If we assign a random number to each row the rows can then be ordered by that value and a selection made from the whole set. In your case, with 400k records, to select 1 in 80 (perhaps why you are looking for every 79th) you can select, say, the first 5000 records to be your random sample.

Note that the final value of the random number displayed will not match the ordering expected, as ordering will force reevaluation of the random numbers. It is nevertheless randomly ordered, as you will see in the sample from the 4000 test records below:

No    Random
3183    0.623072385787964
3557    0.323390901088715
1594    0.144493460655212
823    0.102511465549469
3491    0.401980400085449
2221    0.670504748821259
1706    0.301638245582581
3037    0.285618126392365
636    0.139037370681763
9    0.850202977657318
2981    0.839936137199402
1225    0.688546717166901
1106    0.126784801483154
3422    0.547361075878143
3107    1.72606706619263E-02
3255    0.600176632404327
540    0.311084508895874

The query used for this random sample set is very simple:

SELECT No, fGenerateRandomNumber([No]) AS Random
FROM TestTable
ORDER BY fGenerateRandomNumber([No]);

Note also that you will get a different sample every time you run the query - as no two runs will generate the same set of random numbers.

-Stewart

[Stewart Ross]

Oh, I forgot to mention two things:

Firstly, the function should be placed in any public code module (one that is visible from the Modules tab in Access), or in a new module if one does not already exist.

Secondly, if you really do need to implement some form of 'select the Nth record' routine and not a random sample this can be done in VBA code using a recordset and processing through the records under program control (for example, to place the primary key values of the selected records in another table for later joining back as the selection set). However, I would advise you again that repetitively selecting the Nth record in some sequence is NOT random sampling at all.

-Stewart

[ADezii]

A lot of what I am referring to has already been stated by Stewart, so I'll not state it again. There is a little known Method of retrieving a Random Set of Rows from a Table or Query by using the previously mentioned Public Function approach and the Query's Top Value Property. If you are interested, just let me know, and I'll demonstrate how it is done.

The concept itself is rather quite simple. You add a new Column to the Query, fill it with a List of Random Numbers, and retrieve the Top N Rows, where N is a Number between 1 and the Number of Rows in the underlying data. By specifying the Top N Values for the Query, you are forcing Access to return that many Rows as the result set to the Query. You can also return a certain Percentage of the Total Rows by setting the Top Value to a Percentage (Random Sample of 37% of the Total Number of Records).

[ddtpmyra]

Adenzii,

Please show me how to put it all together.

thanks

[ADezii]

@ddtpmyra
This Demo will display 5 Random Employees, and all their related Fields, from the Employees Table of the Northwind Sample Database:

1. Open the Northwind Sample Database or parallel this Steps in your own Database.
2. Create the following 'Public' Function fGenerateRandom() and pass to it any Field as long as it is contained within the Employees Table. Stewart has already given an excellent explanation as to why this is necessary in Post #2.
   

   Public Function fGenerateRandom(varField As Variant)
     Randomize
     fGenerateRandom = Rnd()
   End Function

3. Create the following Query by Copying-N-Pasting the SQL Statement posted below into the View ==> SQL View Pane of the New Query (Design Mode).
   

   SELECT TOP 5 Employees.*
   FROM Employees
   ORDER BY fGenerateRandom([LastName]);

4. Switch to Design View to see the Graphical representation of the Query in the Query Grid.
5. Look at the Top Values Property of the Query to see the relevance of the 5.
6. Every time you execute this Query, you will see a Random Selection of 5 Employees along with accompanying Fields. The Random Number Field is intentionally Hidden since it serves no purpose displaying it.
7. Any questions feel free to ask, and one of us will get back to you with an answer.

[ddtpmyra]

It works! Thanks for showing it to me Adezii your help is much apreciated and thanks to Stewart for the explaining how it process.

DM

[ADezii]

@ddtpmyra
Glad the old Stew/ADezii combination worked for you (LOL)!