Select Random Record From Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MDW - 14 May 2006 23:14 GMT

Hello.

I'm trying to randomly select one record from an Access table via ASP. I saw
the article on ASPFAQ, but their Access example didn't work for me. I've got
the following code:

Randomize()
intNum = (CInt(1000 * Rnd) + 1) * -1

strSQL = "SELECT TOP 1 CMMT, CMMT_FROM, LOC FROM COMMENT_MASTER ORDER BY
Rnd(ID * " & intNum &") DESC"

ID is an Autonumber field.

This code seems to work, except it always excludes the record with the
highest-numbered ID. (If I remove the "DESC" from the SQL statement, then it
excludes the record with the lowest-numbered ID.

Any suggestions for a simple way to randomly select a record from an Access
2000 table? Thanks.

Signature

Hmm...they have the Internet on COMPUTERS now!

Reply to this Message

Lucas Kartawidjaja - 15 May 2006 00:36 GMT

Hi MDW

Do you by any chance have a fixed number of ID? And is the ID start at 1?
Actually there are a few ways where you can do this in excel. One quick way
to do this is (I am assuming that the COMMENT_MASTER table have the ID column:

Let say that you have 100 ID and the ID is start at 1. Thus, the code would
be:
strSQL = "SELECT CMMT, CMMT_FROM, LOC FROM COMMENT_MASTER WHERE ID =
INT(RND*100) + 1"

You want the random number function (RND) to be multiplied by the number of
ID that you have on your table and add the lowest ID that you have on the
table.

Hopefully that works.

Lucas

> Hello.
>
[quoted text clipped - 16 lines]
> Any suggestions for a simple way to randomly select a record from an Access
> 2000 table? Thanks.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 17 May 2006 17:18 GMT

The problem is there might be gaps. So if you delete the row where id = 78,
and you land on 78 through RND(), you will get an empty result.

> Hi MDW
>
[quoted text clipped - 43 lines]
>> Access
>> 2000 table? Thanks.

Reply to this Message

Lucas Kartawidjaja - 23 May 2006 01:04 GMT

Hi Aaron

You are right, I totally forgot about the gaps problem. Here's the revised
SQL that would solve the gaps problem (I think):

strSQL = "SELECT TOP 1 CMMT, CMMT_FROM, LOC FROM COMMENT_MASTER WHERE ID <=
INT(RND*100) + 1 ORDER BY ID DESC"

Lucas

> The problem is there might be gaps. So if you delete the row where id = 78,
> and you land on 78 through RND(), you will get an empty result.
[quoted text clipped - 46 lines]
> >> Access
> >> 2000 table? Thanks.

Reply to this Message

Webmaster Forum / ASP / Database Access / May 2006

Select Random Record From Access