By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Random Record

P: n/a
Hello,

I am trying to use a SQL Query to return a random record from an
Access 2000 Database.

I am using:

SELECT TOP 1 Example
FROM TABLE
ORDER BY Rnd[(ID)];

But, I am not getting a random record, I get the same record returned
each time. If I create a SQL query in Access, and use the order by
rnd, I will get a random record.

Does anyone know how to return a random record with a SQL query?

Thank you for the help,
Bill Kramer
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
I have no idea on how this works, but I did find a solution (I think)

Try:
Nov 13 '05 #2

P: n/a
Sorry, previous post left me too fast...

Here is the syntax (x = table, ID = field)
SELECT TOP 1 x.ID
FROM x
ORDER BY Rnd([id])*[id]*Now();

This works for me
Nov 13 '05 #3

P: n/a
Thank you very much for your reply. Unfortunately I wasn't able to get
your code to work for me. I haven't found a solution yet.

But thank you very much for your effort.

-Bill Kramer
"WindAndWaves" <ac****@ngaru.com> wrote in message news:<P4******************@news.xtra.co.nz>...
Sorry, previous post left me too fast...

Here is the syntax (x = table, ID = field)
SELECT TOP 1 x.ID
FROM x
ORDER BY Rnd([id])*[id]*Now();

This works for me

Nov 13 '05 #4

P: n/a
This works, but it always pulls the same data! I guess the starting
SEED is not accurate?

WindAndWaves wrote:
Sorry, previous post left me too fast...

Here is the syntax (x = table, ID = field)
SELECT TOP 1 x.ID
FROM x
ORDER BY Rnd([id])*[id]*Now();

This works for me


Nov 13 '05 #5

P: n/a
This works, but it always pulls the same data! I guess the starting
SEED is not accurate?

WindAndWaves wrote:
Sorry, previous post left me too fast...

Here is the syntax (x = table, ID = field)
SELECT TOP 1 x.ID
FROM x
ORDER BY Rnd([id])*[id]*Now();

This works for me


Nov 13 '05 #6

P: n/a
You need to use the Randomize method first. Check the Help file.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"gmsmstr" <dm*******@surdyke.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
This works, but it always pulls the same data! I guess the starting
SEED is not accurate?

WindAndWaves wrote:
Sorry, previous post left me too fast...

Here is the syntax (x = table, ID = field)
SELECT TOP 1 x.ID
FROM x
ORDER BY Rnd([id])*[id]*Now();

This works for me

Nov 13 '05 #7

P: n/a
The ORDER BY clause does not affect which records are chosen, only the order
in which the records that have been selected are presented. You would need a
WHERE or HAVING clause to affect the selection.

Just for the record, RANDOMIZEing prior to executing this SQL will have no
effect on the records returned.

Larry Linson
Microsoft Access MVP
"gmsmstr" <dm*******@surdyke.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
This works, but it always pulls the same data! I guess the starting
SEED is not accurate?

WindAndWaves wrote:
Sorry, previous post left me too fast...

Here is the syntax (x = table, ID = field)
SELECT TOP 1 x.ID
FROM x
ORDER BY Rnd([id])*[id]*Now();

This works for me

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.