Random List in a Report

[Felicia]

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? [sig][/sig]

 

[MichaelRed]

Felicia,

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&quot 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.



[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Felicia]

MichaelRed,

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? [sig][/sig]

 

[MichaelRed]

Flecia,

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 &quot;New&quot; 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 &quot;Make Table&quot; 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.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]