Re: At random selection

This query assumes your table is named "Table1", and it has an AutoNumber
field named "ID":

SELECT TOP 30 Table1.*
FROM Table1
ORDER BY Rnd(Table1.ID), Table1.ID;

For that to be random, you need to issue a Randomize in VBA code before
running the query.

The Rnd() doesn't do anything with the value passed in, but if you don't
pass something the query optimizer is too clever and doesn't bother calling
the function at every row.

The 2nd element of the ORDER BY clause is to prevent more than 30 records
being returned in the (unlikely) event that Rnd() creates duplicates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"IceCube" <anne.decock@xxxxxxxxxx> wrote in message
news:1124351281.362356.62140@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Hello,
>
> I would like to select/filter at random 30 records out of an
> Access-table of 1500 records. I know the option "Top" which gives me
> the possibility to see the 30 first records of the table The problem is
> that this option is linked to the way of sorting the table. So you will
> always select from a very restraint part of the 1500 records.
>
> Is there a possibility
> * to select or sort at random in Access. Maybe there exists an
> sql-syntax?
> * to use a similar function like the funciton Aselect in Excel, which
> gives me the possibility to get an number at random
>
> That's the way I resolve at this moment my problem. I export my table
> to Excel, I accord an number at random (using the function Aselect) to
> each record, I import again in Access and I sort on the number, before
> using the function Top. The next time, I use the same procedure to get
> another set of records.
>
> I assume there must be an easier way to do it?
>
> Is there anybody who knows another solution?
>
> Thanks in advance


.

Relevant Pages

  • At random selection
    ... I would like to select/filter at random 30 records out of an ... * to select or sort at random in Access. ... * to use a similar function like the funciton Aselect in Excel, ... to Excel, I accord an number at random to ...
    (comp.databases.ms-access)
  • At random selection of 30 records in a table of 1500 records
    ... I would like to select/filter at random 30 records out of an Access-table of ... * to select or sort at random in Access. ... * to use a similar function like the funciton Aselect in Excel, ... Excel, I accord an number at random to each ...
    (microsoft.public.access.queries)
  • Re: At random selection of 30 records in a table of 1500 records
    ... SELECT TOP 30 Table1.* ... > * to select or sort at random in Access. ... > * to use a similar function like the funciton Aselect in Excel, ... > Excel, I accord an number at random to each ...
    (microsoft.public.access.queries)
  • Re: update a row in one table with values from muliple rows in another table
    ... This kind of thing sort of goes against the grain. ... you would have already recieved 20 replies saying ... if you are doing this for display ... create table table1 ...
    (microsoft.public.sqlserver.programming)
  • RE: Order By/Filter clause corrupted with aliases
    ... "Boggle" wrote: ... where table1 is always the first joined table with that field name. ... But if you sort in the ... I just wrote a simple cleanup routine (within any pair of square brackets, ...
    (microsoft.public.access.forms)