random sample

J

jdmcleod

I am an experienced Excel user, but am quite new to Access. I am creating a
database for mailing letters to client customers and have encountered a
problem which I don't know how to handle. I am hoping someone can assist me
with this?

Here is what I have:
1. My first table has customer names, account balances, and account numbers.
2. I want to create a query to select a random sample of those customers.
3. I have a form letter that will be filled in with the customer details
from those selected in the sample.

My problem is that I can't write the expression for the query to select a
random sample. Can anyone tell me how to write the expression which would
ask for a parameter value which would be the sample size and then return a
list of randomly selected accounts? Thanks.
 
J

John Vinson

My problem is that I can't write the expression for the query to select a
random sample. Can anyone tell me how to write the expression which would
ask for a parameter value which would be the sample size and then return a
list of randomly selected accounts? Thanks.
Click to expand...

Create a Query based on your table; sort by

Rnd([CustomerID])

and set the Top Values property of the query to the number of records
that you want to export.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
J

John Vinson

Incidentally, assuming that [CustomerID] is positive,
Rnd([CustomerID]) is exactly equivalent to Rnd(), and will produce the
same series of numbers as Rnd() would have!
Click to expand...

I've found that you need to pass a field value as a parameter or
Access will just call Rnd() - or any other parameterless function -
*ONCE*. This is fine if you're calling Date(), but if you want a
different random number on each record, it's a PITA!


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
J

jdmcleod

Hey John and Peter,'
Thanks for your help. I was able to get it to work based on the information
you provided. Thanks again.


John Vinson said:
My problem is that I can't write the expression for the query to select a
random sample. Can anyone tell me how to write the expression which would
ask for a parameter value which would be the sample size and then return a
list of randomly selected accounts? Thanks.
Click to expand...

Create a Query based on your table; sort by

Rnd([CustomerID])

and set the Top Values property of the query to the number of records
that you want to export.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
I

Ian Hunt

Hi Guys,

Just wanted to add my thanks for this information - it has helped me out
tremendously !


jdmcleod said:
Hey John and Peter,'
Thanks for your help. I was able to get it to work based on the information
you provided. Thanks again.


John Vinson said:
My problem is that I can't write the expression for the query to select a
random sample. Can anyone tell me how to write the expression which would
ask for a parameter value which would be the sample size and then return a
list of randomly selected accounts? Thanks.
Click to expand...

Create a Query based on your table; sort by

Rnd([CustomerID])

and set the Top Values property of the query to the number of records
that you want to export.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
Click to expand...
 
Top