Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Random List in a Report

Random List in a Report

Random List in a Report

I don't know how to make a database of students generate a random list for our "Lottery Waiting List."  It always sorts by last or first name or by autonumber which actually puts them in a first entered/first enrolled order and that is what we are trying to avoid.  We need a true "Lottery" list.  Can anyone help?

RE: Random List in a Report


You need to do this in two steps.

First, the generation of some random value.  Here, you can just use the built-in Rnd(), but you need to realize that for every execution, this will return a new value.  Here, "execution" refers to not only the "running" of the query, but also to the display of new records.  This means:

you can't sort the values as you generate them - they all change each time you "look" at (e.g. "sort") them.

So, you need to get the information (except the random value) in a table/query, and then do a MAKE TABLE query from that datasource.  Then, sort/manipulate the results of hte make table.

If this is confusing, please respond in the thread.  I'm sure we can make this work for you, it just might take some time.

There is never time to do it right but there is always time to do it over

RE: Random List in a Report


Thanks for your prompt response.  Here is the deal.  I'm not sure where to put the Rnd() function.

1.  I have a table with fields like:  LastName, FirstName, ParentNames, BirthDate, GraduationYear, Address, Phone, Notes.

2.  I want to run and print a report of students graduating in a certain year that displays: LastName, FirstName, ParentNames, Phone, Notes.

3. The list for the report must be random and should be different each time I run the report.

I have based my report on a parameter query that asks for GraduationYear.  I'm not sure where the random number comes in.  Do I need to create a table that gives a random number to each record and then create a report that sorts by number?  How do I assign a random number to each record?

RE: Random List in a Report


Here is the SQL for a query which generates the random number.

SELECT tblOpnItms.WkNo, tblOpnItms.OpenItems, tblOpnItms.StrtDate, Rnd([OpenItems]) AS RandomId INTO tblRandId
FROM tblOpnItms
ORDER BY Rnd([OpenItems]);

This is a MAKE TABLE query (INTO tblRandID), so the output of the query is a "New" tale in your db. The random function is rnd([OpenItems]) AS RandomId .  This SQL statement is, of course, not related to you data/table/fields, so the only relevant items here are the fact that it is a "Make Table" query and the use of the RND function.

Each time a make table query is run, it makes an entirely new table, so your report needs to be based on the new table name.  For your report to br in a different order each time, you need to re-run the (make table) query.  You can easily add the random number generator to your existing parameter query, and change that to be a make table query as well.  This would leave you with only needing to 'remember' to run the parameter/make table query each time you re-run the report.  Of course, this could also be automated - but it would require some VBA code.

There is never time to do it right but there is always time to do it over

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close