Sort Records into a Random Order

[Nhmiller]

Is there a way to do this? Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Allen Browne]

Assuming you have a primary key field named MyID, try:
SELECT * FROM MyTable ORDER BY Rnd(MyTable.MyI D);

The Rnd() does not actually do anything with the primary key, but if you
don't pass it in, the query optimizer is too intelligent and doesn't bother
calling the function again on every row.

Don't forget to issue a Randomize at the start of your session.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nhmiller" <nh******@aol.c omnojunk> wrote in message
news:20******** *************** ****@mb-m19.aol.com...

Is there a way to do this? Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Nhmiller]

>Assuming you have a primary key field named MyID, try:

SELECT * FROM MyTable ORDER BY Rnd(MyTable.MyI D);

The Rnd() does not actually do anything with the primary key, but if you
don't pass it in, the query optimizer is too intelligent and doesn't bother
calling the function again on every row.

Don't forget to issue a Randomize at the start of your session.

Can you give me detailed directions, starting with which Object to click on (I
assume it is Query)? Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Allen Browne]

1. Make sure your table has an AutoNumber primary key.

2. Create a query into this table.

3. In The Field row, enter:
Rnd([xxxx])
replacing "xxxx" with the name of your primary key field.

4. In the Sorting row under this, enter:
Ascending.

5. Uncheck the box under this in the Show row of the grid.

6. View the output of your query.

This will give you the same seriese of results unless you tell Access to
randomize. To do that:
1. Click the Modules tab of the Database window, and click New.
Access opens a new module.

2. Enter:
Public Function Init()
Randomize
End Function

3. Save the module with a name such as basInit. Close.

4. On the Macro tab of the Database window, click New.
Access opens a new macro.

5. In the Action column, choose RunCode.
In the lower pane enter:
Init()

6. Save the macro with the name AutoExec.
The name is important.

7. Restart your database.

Now each time to you open this database, the randomizer will run.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nhmiller" <nh******@aol.c omnojunk> wrote in message
news:20******** *************** ****@mb-m16.aol.com...

Assuming you have a primary key field named MyID, try:
SELECT * FROM MyTable ORDER BY Rnd(MyTable.MyI D);

The Rnd() does not actually do anything with the primary key, but if you
don't pass it in, the query optimizer is too intelligent and doesn't
bother
calling the function again on every row.

Don't forget to issue a Randomize at the start of your session.

Can you give me detailed directions, starting with which Object to click
on (I
assume it is Query)? Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Nhmiller]

>1. Make sure your table has an AutoNumber primary key.

2. Create a query into this table.

3. In The Field row, enter:
Rnd([xxxx])
replacing "xxxx" with the name of your primary key field.

4. In the Sorting row under this, enter:
Ascending.

5. Uncheck the box under this in the Show row of the grid.

6. View the output of your query.

That all worked. I notice that each time I open the database and click on the
query for the random order, the order is the same. And if I close the query
display of records, and click on the query again, there is a new random order,
and so on. If I reopen the database and start this over again, the sequence of
random order listings is again the same. This still works for me, as I simply
want to mix up the list. However, so I can fully understand how this Rnd
function works, is there a way to get the first display to be a new random
order each time? The second section of your response may be for this. However I
don't want to randomize every time -- I have a second query that puts all the
records in order of entry. In case you're curious, the database is my music CD
collection, and over a period of a couple years, I listen to every CD this way
without spending time pondering about what I feel like hearing. My process was
much more tedious, using a calculator, hitting random keys and multiplying to
generate random numbers. I finally decided to post a message to find a better
way. Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Allen Browne]

Yes, the 2nd part is the answer.

The Randomize affects only the Rnd() function.
Once it has been issued, the Rnd() is seeded differently for that session.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nhmiller" <nh******@aol.c omnojunk> wrote in message
news:20******** *************** ****@mb-m06.aol.com...

1. Make sure your table has an AutoNumber primary key.

2. Create a query into this table.

3. In The Field row, enter:
Rnd([xxxx])
replacing "xxxx" with the name of your primary key field.

4. In the Sorting row under this, enter:
Ascending.

5. Uncheck the box under this in the Show row of the grid.

6. View the output of your query.

That all worked. I notice that each time I open the database and click on
the
query for the random order, the order is the same. And if I close the
query
display of records, and click on the query again, there is a new random
order,
and so on. If I reopen the database and start this over again, the
sequence of
random order listings is again the same. This still works for me, as I
simply
want to mix up the list. However, so I can fully understand how this Rnd
function works, is there a way to get the first display to be a new random
order each time? The second section of your response may be for this.
However I
don't want to randomize every time -- I have a second query that puts all
the
records in order of entry. In case you're curious, the database is my
music CD
collection, and over a period of a couple years, I listen to every CD this
way
without spending time pondering about what I feel like hearing. My process
was
much more tedious, using a calculator, hitting random keys and multiplying
to
generate random numbers. I finally decided to post a message to find a
better
way. Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Nhmiller]

What goes between the () in the following statements:
Public Function Init()
Init()

Thank you.

This will give you the same seriese of results unless you tell Access to
randomize. To do that:
1. Click the Modules tab of the Database window, and click New.
Access opens a new module.

2. Enter:
Public Function Init()
Randomize
End Function

3. Save the module with a name such as basInit. Close.

4. On the Macro tab of the Database window, click New.
Access opens a new macro.

5. In the Action column, choose RunCode.
In the lower pane enter:
Init()

6. Save the macro with the name AutoExec.
The name is important.

7. Restart your database.

Now each time to you open this database, the randomizer will run.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Trevor Best]

Nhmiller wrote:

What goes between the () in the following statements:
Public Function Init()
Init()

Nowt.

--
This sig left intentionally blank

[Nhmiller]

Could not get this to work. Is something to go between the ()? Thanks.

This will give you the same seriese of results unless you tell Access to
randomize. To do that:
1. Click the Modules tab of the Database window, and click New.
Access opens a new module.

2. Enter:
Public Function Init()
Randomize
End Function

3. Save the module with a name such as basInit. Close.

4. On the Macro tab of the Database window, click New.
Access opens a new macro.

5. In the Action column, choose RunCode.
In the lower pane enter:
Init()

6. Save the macro with the name AutoExec.
The name is important.

7. Restart your database.

Now each time to you open this database, the randomizer will run.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Allen Browne]

No. Nothing at all.

If you can't get it to work, you can do this instead, every time you open
your database:
1. Press Ctr+G to open the Immediate window.

2. Type:
Randomize
and press Enter

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nhmiller" <nh******@aol.c omnojunk> wrote in message
news:20******** *************** ****@mb-m14.aol.com...

Could not get this to work. Is something to go between the ()? Thanks.

This will give you the same seriese of results unless you tell Access to
randomize. To do that:
1. Click the Modules tab of the Database window, and click New.
Access opens a new module.

2. Enter:
Public Function Init()
Randomize
End Function

3. Save the module with a name such as basInit. Close.

4. On the Macro tab of the Database window, click New.
Access opens a new macro.

5. In the Action column, choose RunCode.
In the lower pane enter:
Init()

6. Save the macro with the name AutoExec.
The name is important.

7. Restart your database.

Now each time to you open this database, the randomizer will run.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com