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
>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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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?...
|
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
...
|
by: srikanth |
last post by:
please give me any helpful logics for the subject
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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"....
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
| |