Retrieve random record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 29 May 2006 15:25 GMT

I have a table from which I want to retrieve a random record.

The table has an identity (autonumber) field as its primary key and two text
fields. The prmary key values are not contiguous, IOW there are gaps in the
number sequence caused by deletions.

How can I write a query that returns a random record?

Reply to this Message

Allen Browne - 29 May 2006 15:19 GMT

Use Rnd() in the Order clause to select a random record:
SELECT TOP 1 MyTable.*
FROM MyTable
ORDER BY Rnd(MyTable.ID);

You must issue a Randomize before running the query for this to be random.

Signature

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.

>I have a table from which I want to retrieve a random record.
>
[quoted text clipped - 3 lines]
>
> How can I write a query that returns a random record?

Reply to this Message

Dave - 29 May 2006 15:49 GMT

Thanks Allen

That seems to work fine as it is written.

However, I am not sure what you mean by " issue a Randomize before running
the query?"

I cannot find anything on Randomize in the Access Help.

However, under the the Rnd function help at ....

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/v
afctrnd.asp

...it does indeed state:

"Before calling Rnd, use the Randomize statement without an argument to
initialize the random-number generator with a seed based on the system
timer."

I tried preceeding my basic query with SELECT Rnd() but I get an error.

So I guess my question is, how do I issue a Randomize statement?

> Use Rnd() in the Order clause to select a random record:
> SELECT TOP 1 MyTable.*
[quoted text clipped - 10 lines]
>>
>> How can I write a query that returns a random record?

Reply to this Message

Allen Browne - 29 May 2006 15:52 GMT

You can do it by pressing Ctrl+G (to open the Immediate window), typing:
Randomize
and pressing Enter. You will get no response if it worked okay.

The query will then give you random records for the rest of this session
(i.e. until you close Access.) You need to do that again next time. If you
do not issue a Randomize, you will get the same series of "random" numbers
issued each time you start Access.

If you want this to happen each time you start Access, you could create a
function containing the Randomize line. Save it in a module. Create a macro
with the RunCode action to call the function. Name the macro AutoExec so it
automatically executes each time you start Access.

Signature

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.

> Thanks Allen
>
[quoted text clipped - 34 lines]
>>>
>>> How can I write a query that returns a random record?

Reply to this Message

Dave - 29 May 2006 20:57 GMT

Thanks Allen

I am still struggling with this.

I am using Access as the data source for an ASP web page. Every time I run
the query from the ASP page, I get the same record id.

I created a module with the following function:

Public Function CallRandomizer()
Randomize
End Function

I then created a macro named AutoExec that calls CallRandomizer() under the
RunCode action.

I then quit and re-started the database and also closed and reloaded the
browser.

Each time I try to retrieve a random ID from the web page I get the same ID
(186). When I run the query directly from within Access, I always get 186
first, followed by 1291, 1111. If I shut down and restart Access, I will
encounter the same sequence (186, 1291, 1111) after it is re-opened.

So I apparently can't get this to work on any level (ASP or Access).

Do you see what I might be doing wrong?

> You can do it by pressing Ctrl+G (to open the Immediate window), typing:
> Randomize
[quoted text clipped - 48 lines]
>>>>
>>>> How can I write a query that returns a random record?

Reply to this Message

Allen Browne - 30 May 2006 02:20 GMT

If there is no instance of Access running (i.e. you are just connecting to
the data from different software), then it will not be possible to issue a
Randomize in Access, so you will get the repeating number.

Perhaps you could ask in an ASP group how to get a random number. (You may
have to do something like opening a recordset, moving to the last record,
getting the record count, and moving back a random number of records.)

Signature

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.

> Thanks Allen
>
[quoted text clipped - 76 lines]
>>>>>
>>>>> How can I write a query that returns a random record?

Reply to this Message

Wei Lu - 30 May 2006 05:36 GMT

Hi Dave,

I think you could use the Randomize function in the ASP script.

Here are some articles for your reference.

http://groups.google.com/group/microsoft.public.inetserver.asp.db/browse_frm
/thread/1191db894f771e58/282e6c3694299be8

http://groups.google.com/group/microsoft.public.scripting.vbscript/browse_fr
m/thread/67b6295ccaa7ca26/fddeeaa2f3ce9dfb

Hope this will be helpful.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message