random sample

[Guest]

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.

 

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

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

 

[Peter R. Fletcher]

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.

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

If you frequently need to do this, it may be worth putting a field in
your Customers Table which defaults to Rnd(), and indexing it.
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!

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[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!

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

 

[Peter R. Fletcher]

You're right, of course, if you do it from a query column, as you were
in fact suggesting - Access sometimes optimises Queries a little too
well (!). If you use it elsewhere (e.g. as I was suggesting) it
doesn't need or use the parameter.

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!

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

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Guest]

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

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.

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

 

[Guest]

Hi Guys,

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

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

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.

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