Select Random Record From Access

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.
--
Hmm...they have the Internet on COMPUTERS now!

RE: Select Random Record From Access

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


"MDW" wrote:

> 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.
> --
> Hmm...they have the Internet on COMPUTERS now!

Re: Select Random Record From Access

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.




"Lucas Kartawidjaja" wrote in
message news:974A816D-C701-44AF-9E85-807AEF197012@microsoft.com...
> 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
>
>
> "MDW" wrote:
>
>> 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.
>> --
>> Hmm...they have the Internet on COMPUTERS now!

Re: Select Random Record From Access

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


"Aaron Bertrand [SQL Server MVP]" wrote:

> 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.
>
>
>
>
> "Lucas Kartawidjaja" wrote in
> message news:974A816D-C701-44AF-9E85-807AEF197012@microsoft.com...
> > 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
> >
> >
> > "MDW" wrote:
> >
> >> 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.
> >> --
> >> Hmm...they have the Internet on COMPUTERS now!
>
>
>