Random Record

[Bill]

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

[WindAndWaves]

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

Try:

[WindAndWaves]

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

[Bill]

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
"WindAndWav es" <ac****@ngaru.c om> wrote in message news:<P4******* ***********@new s.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

[gmsmstr]

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

[gmsmstr]

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

[PC Datasheet]

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******@pcdata sheet.com
www.pcdatasheet.com
"gmsmstr" <dm*******@surd yke.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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

[Larry Linson]

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*******@surd yke.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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