Random Sampling

[zzetta]

I would like to pull a random sample out of my databases. I know how to do it
in Excel, but would like to be able to do it in Access without having to
export. Is there a way in Access? Please help.

 

[Roger Carlson]

Actually, it's quite simple.

Suppose I had a table called Table1 with a numeric field called Table1_ID
and I wanted 10 random records. The query would look like this:

SELECT TOP 10 * FROM Table1 ORDER BY Rnd(Table1_ID)

Ordinarily, the TOP query would select 10 records as determined by values of
the field you are sorting on (the Order By clause), but when you order on a
random number (as created by the Rnd function seeded with a numeric value
from the record), the net effect is a random sampling.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Tom Wickerath MDB]

I believe that the function is actually called RAND()

not RND

look it up in Books Online; since Access Data Projects are the default
database for Access 2002 and 2003; everyone should be using SQL Server and
not Jet SQL

 

[Dancing Queen]

if you want random sampling then you've got to use SQL Server and Acess Data
Projects

 

[Bunky]

Mr. Carlson,

I too have a need for a random sampling from a table called
Reservations_New. What I want to sample are reservation numbers. This field
is a text field because there are alpha characters in the reservation number.
I tried the Select you so kindly wrote and got a compile error. I've been
to several sites trying to find the syntax for the
ORDER BY (Rnd(Proj) *0 + 1);
Line. The 'Proj' is a numeric field. Can you point me in the right
direction?

Thank you for your time.

Kent