Extracting Random Records 1

[LakotaMan]

Is there a simple function or expression I can use in a query to extract random records from a table?

 

[TCVance]

One way you might do it is to add an autonumber field to your table and have it increment by random.

 

[LakotaMan]

Sounds good, but I would like a query to extract a different set of records every time I run it. How will that work with the autonumber set at random?

 

[epoirier]

Me i suggest to return the RecordCount and made a Random
with the instruction (RND) Like This Int((RecordCount * Rnd) + 1.

See Rnd Function in help file.

Here i made some Fast Code i Think is Worked.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim iRecordCount As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("Table1", dbOpenSnapshot)

With rst
If .RecordCount > 0 Then
.MoveLast
'Get Last Record.
iRecordCount = .RecordCount

'Position on First Record.
.MoveFirst

'Choose a number between 0 and Last Record -1
.Move (Int(Rnd * .RecordCount))

'Display Fields Name
MsgBox ![MyData]
Else
End If
.Close: Set rst = Nothing
End With

db.Close: set db=Nothing

 

[mikevh]

In a query, you could say something like

SELECT TOP N fieldlist
FROM tablename
ORDER BY Rnd(numericfield);

where

N is the number of records you want to select
fieldlist is the fields you want in your recordset
tablename is of course your table
numericfield is some numeric field in your table

I've used this method before and it works well.

 

[LakotaMan]

Thanks to all who contributed! I found, however, mikevh's SQL statement to be the easiest and works like a charm!

Thanks again, mikevh!