Random Sampling

Z

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.
 
R

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
 
T

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
 
D

Dancing Queen

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top