Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Selecting a random row in an MS Access database

Posted on 2006-11-17
23
Medium Priority
?
2,142 Views
Last Modified: 2012-05-05
I can't figure out why this SQL statement isn't working - it returns the same value every time.

SELECT Field1 FROM Table1 ORDER BY rnd()

I also tried

SELECT Field1 FROM Table1 ORDER BY rnd(NumericValue)

It returns the same result every time as well (not the same result as the first SQL statement, but the same one everytime the query is run.)

Any help would be appreciated.
0
Comment
Question by:filemanager
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 5
  • +3
23 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17970599
md() appears to be returning only one value

maybe you intended the following:

SELECT Field1 FROM Table1 ORDER BY rnd(Field1)
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 17970633
Look up my comment here:

http://www.experts-exchange.com/Databases/Q_21713793.html

It explains how and why.

/gustav
0
 
LVL 46

Expert Comment

by:aikimark
ID: 17971242
SELECT Field1, rnd() as RandNo FROM Table1 ORDER BY 2
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 46

Expert Comment

by:aikimark
ID: 17971253
Correction:
SELECT Field1, rnd([ID]) as RandNo FROM Table1 ORDER BY 2

Where [ID] is the autonumber column in the table
0
 

Author Comment

by:filemanager
ID: 17973137
I've tried all of your suggestions, and all of them return the same value every time :(
0
 
LVL 58

Expert Comment

by:harfang
ID: 17973449
Hello filemanager

All of the above solutions worked for me. You need only two things: a) provide a field as parameter to force rnd() to recompute on each row, and b) make sure that the number is positive. That's all. Even Rnd(Val("1" & strJustAnyField)) works if you do not have an autonumber.

(°v°)
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 17974025
Sorry, but none of the methods using Rnd() "as is" directly in SQL will work.
With Access JET tables you have to use an external function as shown in the link provided above.
With SQL Server you have other options but that's another topic.

The link shows how to pick one (or more) unique random records.
In your case you request all records, thus it can simplified like this:

  SELECT
    Field1
  FROM
    Table1
  ORDER BY
    RandomNumber([ID] Is Null);

where ID is the primary key of your table.

/gustav
0
 
LVL 58

Accepted Solution

by:
harfang earned 100 total points
ID: 17976648
cactus_data,

I beg to differ, but the following query gives me five random suppliers, different every time.

SELECT TOP 5 SupplierID, CompanyName
FROM Suppliers
ORDER BY Rnd(SupplierID);

It is true that Rnd() alone does not work, because the query optimizer will call it only once, but as soon as you provide a positive integer, using a field name in the expression, Rnd(<expr>) will be called for each record, and return the "next" random number at every call.

(°v°)
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 17977788
It may appear so, but if you run the query, record the resultset, close Access, then relaunch and run the query again, you will notice that the query returns the exact same resultset as before.

That's because Rnd() is not a function returning random numbers, only pseudo random numbers. To partially overcome this, VBA provides Randomize which you must call to obtain a unique sequence based on the current time which, of course, will be different each time you call Randomize. You might think that you could call Randomize from the query via a wrapping function - and you can do so - but it has no effect as it will be run in another scope than Rnd().

Thus, to obtain a different pseudo random sequence any time you run the query no matter if Access has been relaunched or not, you have to draw the sequence from an external helper function.

/gustav
0
 
LVL 58

Expert Comment

by:harfang
ID: 17978512
cactus_data,

Thank you for the clarification, I understand what you were getting at now. And since Randomize is not implemented as a function, there is indeed no other solution as the one you provide.

(°v°)
0
 
LVL 46

Expert Comment

by:aikimark
ID: 17979213
If you only want to see ONE ROW and there is an autonumber column, you can use the following:
SELECT Top 1 Field1
FROM Table1
WHERE ID > (Rnd()*(Select Count(*) From Table1))
0
 

Author Comment

by:filemanager
ID: 18141810
Ok... I got the random SQL statement to work in MS Access, but when it's run from within VB it doesn't work... anyone know why?
0
 
LVL 58

Expert Comment

by:harfang
ID: 18344101
Well, cactus_data did provide a full and detailed solution. But than again, there was no follow-up to the author's last succinct comment, mostly because the "why" was covered amply...
No objections from my part, but I would have recommended {http:#17974025} as the accepted solution.
(°v°)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18344433
>there was no follow-up to the author's last succinct comment
That's why I recommended a refund...  Lets see if the author rersponds.
0
 

Author Comment

by:filemanager
ID: 18346247
I ended up using puppydogbuddy's suggestion, in addition to adding:


        intRandomNumber = .RecordCount
        intRandomNumber = Int(intRandomNumber * Rnd) + 1
        For i = 1 To (intRandomNumber - 1)
            .MoveNext
        Next

after I opened the recordset. For some reason it always chose the same record, so the code above picked a random record out of the recordset.
0
 

Author Comment

by:filemanager
ID: 18346253
Clairification: I used a modified version of puppydogbuddy's suggestion.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18346425
filemanager,
You can select an answer yourself following these closing and grading guidelines:
http://www.experts-exchange.com/help.jsp#hs5
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 18347871
> when it's run from within VB it doesn't work... anyone know why?

Sorry, missed that.

That's because you have to use similar code to what I've suggested also if you move your code to VB.
Indeed, a call to Randomize is mandatory. It not, you have your reason why:

> it always chose the same record, ..

/gustav
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 18348892
> I used a modified version of puppydogbuddy's suggestion

So no points for Richard.

/gustav
0
 
LVL 58

Expert Comment

by:harfang
ID: 18353383
Oh... did you really intend to accept my answer? It seems my misconception has been corrected by gustav in detail!
(°v°)
0
 

Author Comment

by:filemanager
ID: 18356524
harfang, yes I meant to. I looked at the code I used and your suggestion was the closest to what I actually got working.
0
 
LVL 58

Expert Comment

by:harfang
ID: 18358619
OK, then. Success with your project!
(°v°)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question