473,289 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Sort Records into a Random Order

Is there a way to do this? Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com
Nov 13 '05 #1
22 17756
Assuming you have a primary key field named MyID, try:
SELECT * FROM MyTable ORDER BY Rnd(MyTable.MyID);

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

Nov 13 '05 #2
>Assuming you have a primary key field named MyID, try:
SELECT * FROM MyTable ORDER BY Rnd(MyTable.MyID);

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
Nov 13 '05 #3
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.comnojunk> 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.MyID);

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

Nov 13 '05 #4
>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
Nov 13 '05 #5
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.comnojunk> 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

Nov 13 '05 #6
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
Nov 13 '05 #7
Nhmiller wrote:
What goes between the () in the following statements:
Public Function Init()
Init()


Nowt.

--
This sig left intentionally blank
Nov 13 '05 #8
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
Nov 13 '05 #9
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.comnojunk> 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

Nov 13 '05 #10
I've been trying to find a book/manual that explains how to use Access beyond
the most elementary level. Or is there a website that has the answers? What do
you use? The posts I've made here are for operations that should be in the Help
menus, but are not. I'm realizing that Access works by right clicking in
Design, choose Properties, then knowing what to do or select to get the Object
to display what I want. But it is rarely obvious what to do or select. Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com
Nov 13 '05 #11
Answered in your later thread
Nov 13 '05 #12
>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


Still does not work. Each time I open Access and open the data base, then
select the query called Random records, the order is the same, with or without
doing the above. Pressing Enter just creates a line feed, and I don't see how
to run what is in the immediate window. Let me know if anything missing or
needing more explanation in this or previous instructions. Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com
Nov 13 '05 #13
Nhmiller wrote:
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


Randomize Timer
--
This sig left intentionally blank
Nov 13 '05 #14
>>>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


Randomize Timer


Tried Randomize Timer instead of Randomize, but the random order of the records
is still the same.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com
Nov 13 '05 #15
Nhmiller wrote:
Tried Randomize Timer instead of Randomize, but the random order of the records
is still the same.


Too right, I just tried it, Looks like Randomize is broken :-( (or I'm
stuck in a time loop)

I have another app that I didn't notice this in, I put a field in the
table called "Rand" (Number: Long Integer) and then update it thus:

Function RandomizeTable(pstrTable As String)
Dim rst As Recordset
Dim db As Database
Set Db = CurrentDB()
Set rst = db.OpenRecordset("select * from " & pstrTable & " order
By Rand", dbOpenDynaset)
Randomize Timer
With rst
.MoveFirst
Do Until .EOF
.Edit
!Rand = CLng(Rnd * 100)
.Update
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set Db = Nothing
End Function

This appears to work better as it's sorted by the random number each
time before applying the next random number. As the number of records in
the table grows, it'll shuffle it more.
--
This sig left intentionally blank
Nov 13 '05 #16
In the query, you did put your a field that contains a unique numeric value
inside the Rnd()?

--
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.comnojunk> wrote in message
news:20***************************@mb-m20.aol.com...
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


Randomize Timer


Tried Randomize Timer instead of Randomize, but the random order of the
records
is still the same.

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

Nov 13 '05 #17
On 15 Nov 2004 04:09:16 GMT, nh******@aol.comnojunk (Nhmiller) wrote:
Is there a way to do this? Thanks.

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


Hi
Add a random autonumber field to one of the tables, Sort the query on
this.
David
Nov 13 '05 #18
>In the query, you did put your a field that contains a unique numeric value
inside the Rnd()?


If I understand what you are trying to ask (there's a few extra words in your
question) you're asking me if I put anything between the (). I did not.
Otherwise, restate the question, please. Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com
Nov 13 '05 #19
That is correct.

If you look back to the earlier example, it suggested using this in your
query:
Rnd([ID])
where ID is the name of the primary key field of your table.

In practice, the ID can be any number that is different on every row of the
query, so the primary key is the simplest solution.

If you do not supply something as an argument for Rnd(), the query optimizer
thinks that the function call will be the same as last time, and does not
bother calling the function again.

--
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.comnojunk> wrote in message
news:20***************************@mb-m20.aol.com...
In the query, you did put your a field that contains a unique numeric
value
inside the Rnd()?


If I understand what you are trying to ask (there's a few extra words in
your
question) you're asking me if I put anything between the (). I did not.
Otherwise, restate the question, please. Thanks.

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

Nov 13 '05 #20
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:41***********************@per-qv1-newsreader-01.iinet.net.au:
That is correct.

If you look back to the earlier example, it suggested using this in
your query:
Rnd([ID])
where ID is the name of the primary key field of your table.

In practice, the ID can be any number that is different on every row
of the query, so the primary key is the simplest solution.

If you do not supply something as an argument for Rnd(), the query
optimizer thinks that the function call will be the same as last time,
and does not bother calling the function again.


Sub temp()
Dim p As Single
Dim r As Single
Dim z As Long
For z = 1 To 1000000
If p > r Then Exit For
r = Rnd(z)
p = r
Next z
MsgBox z
' I always get 1000001
End Sub

It seems that

x2 > x1 => rnd(x2) > rnd(x1)
(I've checked on VBA and MS-SQL).

If that is so, then a sort based on rnd(autonumber) of an set of records
ordered by an autonumber key would not change the order?

Of course, there is no such thing as a random number. Every number we
use or conceive has some sort of seed or antecedent.

In MS-SQL I have used NewID() but I don't know how effective that is
generally; it was satisfactory for the situation.

I suppose if I had to do this I would sort on some XOR with some string
taken from something that seems unpredictable if not random: a read from
a memory location, the first word in the text of the front page of
Pravda, the number of people killed in Iraq since the invasion, or
whatever. But that's off the top of my head.

Nov 13 '05 #21

"Lyle Fairfield" <do******@me.com> wrote in message
news:Xn**********************************@216.221. 81.119...

Sub temp()
Dim p As Single
Dim r As Single
Dim z As Long
For z = 1 To 1000000
If p > r Then Exit For
r = Rnd(z)
p = r
Next z
MsgBox z
' I always get 1000001
End Sub
Lyle, you were going too fast and not your usual self. In the above code p
is always set equal to r at the bottom of the loop, and remains so in the
early exit test.

It seems that

x2 > x1 => rnd(x2) > rnd(x1)


At the debug prompt, ?Rnd(3) may or may not be less than ?Rnd(4)

- Steve

Nov 13 '05 #22
"Stephen K. Young" <s k y @ stanleyassociates . com> wrote in
news:30*************@uni-berlin.de:

"Lyle Fairfield" <do******@me.com> wrote in message
news:Xn**********************************@216.221. 81.119...

Sub temp()
Dim p As Single
Dim r As Single
Dim z As Long
For z = 1 To 1000000
If p > r Then Exit For
r = Rnd(z)
p = r
Next z
MsgBox z
' I always get 1000001
End Sub


Lyle, you were going too fast and not your usual self. In the above code
p is always set equal to r at the bottom of the loop, and remains so in
the early exit test.

It seems that

x2 > x1 => rnd(x2) > rnd(x1)


At the debug prompt, ?Rnd(3) may or may not be less than ?Rnd(4)

- Steve


ArgggggggggggH! My Bad.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.
Nov 13 '05 #23

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Jefferis NoSpamme | last post by:
I have a query that is showing new items in a store based upon their inventory date. The problem with my Random order sort is that when the "next" link is pressed, the random function is called...
3
by: Lad | last post by:
What is the best( easiest)way how to sort a file? I have a file where each record consists of 3 fields( 3 words) and I would like to sort records by the first field( word)in each record. Any idea?...
2
by: Paul Evans | last post by:
Hi, I've created a datagrid which works fine accept the id field (the primary key field) comes out in a random order. How do I get the fields to display sequencially? Thanks for your time ...
3
by: srikanth | last post by:
please give me any helpful logics for the subject
6
by: InnoCreate | last post by:
Hi everyone. I've recently written a classic asp website which uses an MS Access datasource. I know this is less than an ideal data source as it has limited functionality. I have a search form on...
4
by: kevincw01 | last post by:
Anyone have a clever way to retrieve for example, items 0-29 from an array of size N>29, in random order? The catch is that I dont want to print any items more than once and I dont want to miss...
9
by: Tuxedo | last post by:
I'd like to reorganize the third, fourth, fifth and sixth, as well as any elements thereafter in an array in random order: var a = new...
2
by: federicog | last post by:
I have to create a listing in random order from a few mysql tables. I can't use ORDER BY RAND() for a few reasons: - The random order has to change only every 24hs, ie: today it will show rows...
2
by: dniom | last post by:
Hi. Can you help me please? Is there any way to show a unique random item from an array, every time you refresh the page? In other words how do I show the items from an array in random order, one by...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.