22  16784 
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
>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
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
>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
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
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
Nhmiller wrote: What goes between the () in the following statements:
Public Function Init()
Init()
Nowt.
--
This sig left intentionally blank
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
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
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
Answered in your later thread
>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
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
>>>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
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
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
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
>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
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
"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.
"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
"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. This discussion thread is closed Replies have been disabled for this discussion. Similar topics
4 posts
views
Thread by Jefferis NoSpamme |
last post: by
|
3 posts
views
Thread by Lad |
last post: by
|
2 posts
views
Thread by Paul Evans |
last post: by
|
3 posts
views
Thread by srikanth |
last post: by
|
6 posts
views
Thread by InnoCreate |
last post: by
|
4 posts
views
Thread by kevincw01 |
last post: by
|
9 posts
views
Thread by Tuxedo |
last post: by
| | | | | | | | | | | | | | | | | | | | | | | |
