How to select random records

[mr.splarf]

Does anyone know what SQL I should use in Access to
return a random set of records ?
i.e. everytime the user presses the refresh button on a
web-page, I want the SQL to select x records completely
at random from a given table.
The records returned should be randomly chosen each time
the select is run (ie not reiterate the same random
numbers each time).
I've got a table with about 40,000 records, and I want a
random 10 records, so obviously I don't want to select
everything and then pick 10 at random afterwards; I want
the initial SQL to just pick 10 records at random and
then return them.
Thanks.

 

[[MVP] S. Clark]

SELECT TOP 10 [OrderID] Mod (9*Rnd()+1) AS Expr1, Orders.OrderID
FROM Orders
WHERE ((([OrderID] Mod (9*Rnd()+1))=0))
ORDER BY [OrderID] Mod (9*Rnd()+1), Orders.OrderID DESC;

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-