Needed:Random rows....

[Obfuscated.f1]

Hello and thanks in advance,


I need the rows in my table to be randomly accessed. I am thinking this
can be done with on the retrieval. Does anyone know of a
retrival/select statement whose access is random?

I am building a potienal-customer database that will be split up evenly
between several sales associates.

Highest regards,


T.

 

[Allen Browne]

Use Rnd() in the ORDER BY clause to get a random sort:
SELECT * FROM Table1 ORDER BY Rnd(Table1.ID);

Notes:
1. You must issue a Randomize before executing the query.

2. Rnd() does not actually do anything with the number you pass in, but you
need to pass one. Otherwise the query optimiser does not bother calling the
query on every row.

 

[Obfuscated.f1]

Yes that appears to be working. Verifying the results now (as best i
can)

Thanks for the reponse.

 

[Obfuscated.f1]

Here is the script:

SELECT Dade.* INTO Dade_Sfr_RND_TMP
FROM Dade
WHERE (((Dade.[Land Use])="Sfr") AND ((Dade.[Owner Name])<>' '))
ORDER BY Rnd(Dade.ID);



Two things:

The script generates the same results with each execution
The results are in an ascending order

 

[Allen Browne]

Did you execute a Randomize first?

Are the random results sorting in ascending order *after* the append, i.e.
because of the way Access displays the new table?

Would it work better to create a table ready to receive the output, with an
AutoNumber primary key, and then use an Append query to add the records to
this existing table?

 

[Access Headache # Rnd() Not]

Finally! I might find the answer that has caused me to go bald!

Along with all the documentation on and offline you have stated (or asked in
this case) "Execute a Randomize first"

PLEASE tell me exactly HOW you "Execute a Randomize first"

I am trying to do a query that will randomly pick a record - and everywhere
I read it says you must "Execute a Randomze first" but NOWHERE does it say
where or how to "Execute a Randomize first".

I cannot seem to get it to work in the actual expression line and I know
nothing about using Macros and/or Modules.

Your help is greatly appreciated!

Thank you!

Did you execute a Randomize first?

Are the random results sorting in ascending order *after* the append, i.e.
because of the way Access displays the new table?

Would it work better to create a table ready to receive the output, with an
AutoNumber primary key, and then use an Append query to add the records to
this existing table?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the script:

SELECT Dade.* INTO Dade_Sfr_RND_TMP
FROM Dade
WHERE (((Dade.[Land Use])="Sfr") AND ((Dade.[Owner Name])<>' '))
ORDER BY Rnd(Dade.ID);

Two things:

The script generates the same results with each execution
The results are in an ascending order

 

[Douglas J Steele]

See http://www.mvps.org/access/queries/qry0011.htm at "The Access Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Access Headache # Rnd Not" <Access Headache # Rnd()
[email protected]> wrote in message

Finally! I might find the answer that has caused me to go bald!

Along with all the documentation on and offline you have stated (or asked in
this case) "Execute a Randomize first"

PLEASE tell me exactly HOW you "Execute a Randomize first"

I am trying to do a query that will randomly pick a record - and everywhere
I read it says you must "Execute a Randomze first" but NOWHERE does it say
where or how to "Execute a Randomize first".

I cannot seem to get it to work in the actual expression line and I know
nothing about using Macros and/or Modules.

Your help is greatly appreciated!

Thank you!

Did you execute a Randomize first?

Are the random results sorting in ascending order *after* the append, i.e.
because of the way Access displays the new table?

Would it work better to create a table ready to receive the output, with an
AutoNumber primary key, and then use an Append query to add the records to
this existing table?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the script:

SELECT Dade.* INTO Dade_Sfr_RND_TMP
FROM Dade
WHERE (((Dade.[Land Use])="Sfr") AND ((Dade.[Owner Name])<>' '))
ORDER BY Rnd(Dade.ID);

Two things:

The script generates the same results with each execution
The results are in an ascending order

 

[Access Headache # Rnd() Not]

I saw that and have been trying to implement it -

Most people think I am a nerd but when I know the truth!

I have yet to get it to work - I am getting this error:

Syntax error (missing operator) in query expression
'DealersToUse.[Dealer #] where randomizer()=0
order by rnd(isnull)DealersToUse.[Dealer #] * 175 + 1)'

See http://www.mvps.org/access/queries/qry0011.htm at "The Access Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Access Headache # Rnd Not" <Access Headache # Rnd()
[email protected]> wrote in message

Finally! I might find the answer that has caused me to go bald!

Along with all the documentation on and offline you have stated (or asked in
this case) "Execute a Randomize first"

PLEASE tell me exactly HOW you "Execute a Randomize first"

I am trying to do a query that will randomly pick a record - and everywhere
I read it says you must "Execute a Randomze first" but NOWHERE does it say
where or how to "Execute a Randomize first".

I cannot seem to get it to work in the actual expression line and I know
nothing about using Macros and/or Modules.

Your help is greatly appreciated!

Thank you!

Did you execute a Randomize first?

Are the random results sorting in ascending order *after* the append, i.e.
because of the way Access displays the new table?

Would it work better to create a table ready to receive the output, with an
AutoNumber primary key, and then use an Append query to add the records to
this existing table?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the script:

SELECT Dade.* INTO Dade_Sfr_RND_TMP
FROM Dade
WHERE (((Dade.[Land Use])="Sfr") AND ((Dade.[Owner Name])<>' '))
ORDER BY Rnd(Dade.ID);

Two things:

The script generates the same results with each execution
The results are in an ascending order

 

[Access Headache # Rnd() Not]

I just found a different solution as follows:

SELECT TOP 1 *
FROM table
ORDER BY RndNum([keyfield])
(Thanks to MVP Marsh)

The module is:
Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

(Thanks to MVP John W. Vinson)

found it in here -

this one is working fantastic - thanks for all the help -

I would still like to know what I was doing wrong with the other one so I
can learn thanks again

I saw that and have been trying to implement it -

Most people think I am a nerd but when I know the truth!

I have yet to get it to work - I am getting this error:

Syntax error (missing operator) in query expression
'DealersToUse.[Dealer #] where randomizer()=0
order by rnd(isnull)DealersToUse.[Dealer #] * 175 + 1)'

See http://www.mvps.org/access/queries/qry0011.htm at "The Access Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Access Headache # Rnd Not" <Access Headache # Rnd()
[email protected]> wrote in message

Finally! I might find the answer that has caused me to go bald!

Along with all the documentation on and offline you have stated (or asked in
this case) "Execute a Randomize first"

PLEASE tell me exactly HOW you "Execute a Randomize first"

I am trying to do a query that will randomly pick a record - and everywhere
I read it says you must "Execute a Randomze first" but NOWHERE does it say
where or how to "Execute a Randomize first".

I cannot seem to get it to work in the actual expression line and I know
nothing about using Macros and/or Modules.

Your help is greatly appreciated!

Thank you!

:

Did you execute a Randomize first?

Are the random results sorting in ascending order *after* the append, i.e.
because of the way Access displays the new table?

Would it work better to create a table ready to receive the output, with an
AutoNumber primary key, and then use an Append query to add the records to
this existing table?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the script:

SELECT Dade.* INTO Dade_Sfr_RND_TMP
FROM Dade
WHERE (((Dade.[Land Use])="Sfr") AND ((Dade.[Owner Name])<>' '))
ORDER BY Rnd(Dade.ID);

Two things:

The script generates the same results with each execution
The results are in an ascending order

 

[John Vinson]

Syntax error (missing operator) in query expression
'DealersToUse.[Dealer #] where randomizer()=0
order by rnd(isnull)DealersToUse.[Dealer #] * 175 + 1)'

I see you're now successfully using my RndNum() function but just FYI
- this isn't working because the expression

rnd(isnull)DealersToUse.[Dealer #]

cannot be interpreted ( by the compiler, or even by me!)

IsNull() is a function which takes an argument. I'm GUESSING that what
was intended was

Order By Rnd(IsNull([DealersToUse].[Dealer #]))

The IsNull function will return either 0 or -1; pass that as an
argument to the Rnd() function; and sort by that random number. It
doesn't deal with the Randomize() operation but that's handled in the
(clever!) Where condition.

John W. Vinson[MVP]

 

[Access Headache # Rnd() Not]

John thank you very much on two counts -
1. the code that worked for me (and I did not manage to mess up)
2. for pointing out my syntax error on the first method - I see it now
(after actually being more awake instead of the 3am I orignally woke up this
morning on and was trying to do things)

thanks again! I have now started to grow hair again!

Syntax error (missing operator) in query expression
'DealersToUse.[Dealer #] where randomizer()=0
order by rnd(isnull)DealersToUse.[Dealer #] * 175 + 1)'

I see you're now successfully using my RndNum() function but just FYI
- this isn't working because the expression

rnd(isnull)DealersToUse.[Dealer #]

cannot be interpreted ( by the compiler, or even by me!)

IsNull() is a function which takes an argument. I'm GUESSING that what
was intended was

Order By Rnd(IsNull([DealersToUse].[Dealer #]))

The IsNull function will return either 0 or -1; pass that as an
argument to the Rnd() function; and sort by that random number. It
doesn't deal with the Randomize() operation but that's handled in the
(clever!) Where condition.

John W. Vinson[MVP]

 

[Muthu]

Syntax error (missing operator) in query expression
'DealersToUse.[Dealer #] where randomizer()=0
order by rnd(isnull)DealersToUse.[Dealer #] * 175 + 1)'

I see you're now successfully using my RndNum() function but just FYI
- this isn't working because the expression

rnd(isnull)DealersToUse.[Dealer #]

cannot be interpreted ( by the compiler, or even by me!)

IsNull() is a function which takes an argument. I'm GUESSING that what
was intended was

Order By Rnd(IsNull([DealersToUse].[Dealer #]))

The IsNull function will return either 0 or -1; pass that as an
argument to the Rnd() function; and sort by that random number. It
doesn't deal with the Randomize() operation but that's handled in the
(clever!) Where condition.

John W. Vinson[MVP]