Create and order by random number in query field

[jobrien4]

I'm trying to create a query that generates random numbers for each record, sorts them by that field, then selects the top record. This should randomize the record being selected.

I can use the Rnd([ID]) function which does appear to generate a random number. Problem is that each time I exit the program and come back in, it always selects the same record. When I remove the Top = 1, to show all the records, every row does have a different random number but it does not appear to be sorting by this field.

If I run the query, here is the number I get:
0.98609316349029


Exit the program, restart, and run the query again:
0.98609316349029

If I refresh the query, the second and third time does appear to be random but the first result is always the same. Any ideas on how to generate truly random numbers?

 

[plog]

I'm running a test and mine is working. Do you have an ORDER BY clause in your SQL? Or are you manually sorting by the random field and hoping it sticks? What's your SQL?

Here's mine:

SELECT ID
FROM TableName
ORDER BY Rnd([ID]) DESC;

Everytime I run it a different ID is at the top.

 

[jobrien4]

If I refresh the query, or close the query then re-run, it does generate a new top record.

If I close the entire database then re-open and run the query, it goes back to the initial record every time.

Here is my SQL (loc_ref_ctl_no is a unique identifier):

SELECT TOP 1 informix_inrloc_rec.loc_whs_zn, informix_inrloc_rec.loc_loc, Rnd([loc_ref_ctl_no])
FROM informix_inrloc_rec
ORDER BY Rnd([loc_ref_ctl_no]);

 

[jobrien4]

I took pictures showing the results of that SQL if I remove the Top 1 clause. As you can see, it does not appear to be sorting by the Rnd(ID) field. Also I closed database, re-ran the query and took another picture. Exact same numbers are generated. See attachments

 

[plog]

Neat, mine is doing the exact same thing. Upon opening the database the first sort is always the same, so is the second sort, and the third. It seems that somehow Rnd() is tied to the number of times it is run.

I think you are going to need to build your own Rnd() function--somehow taking the existing Rnd() and making it more random. Either by seeding it with the time, or possibly with the size of a table that constantly changes.

I'd search this forum for code that does this.

 

[jdraw]

Here's an article on Random numbers and Access.

See Randomize.

 

[jobrien4]

The article explained the Rnd function but didn't really give a solution to how to avoid getting the same first seed each time.

The 4th post on this thread gives the solution with creating your own randomizing function with this code in a new module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

http://www.pcreview.co.uk/forums/make-query-sort-randomly-each-time-run-t4001019.html

 

[AccessBlaster]

It's pretty simple once you create the module and the calculated field. See sample.
Note if you have a small sample like mine (5) records, you may get the same result over and over.