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?