Sort Order - random?

[Greg Brady]

Is there a way to return a random sort order from a query?

[John Baker]

Re: Sort Order - random?

Greg Brady wrote:[color=blue]
> Is there a way to return a random sort order from a query?[/color]

Create a function that returns a random number between 1 and the maximum
number of rows your query is likely to return. You can base this
dynamically on the tables you are querying if you wish. Have this
function create a column in the query. Sort by this column.

[Jeffrey R. Bailey]

Re: Sort Order - random?

In Access SQL it is possible to call certain VB Functions directly. In this
case the Rnd function can be called in a query and fed a seed value from the
data being sorted. Here is an example to randomly sort some invoice
numbers:

SELECT xTstSource.Invo iceNo, Rnd([InvoiceNo]) AS RndOrder
FROM xTstSource
ORDER BY Rnd([InvoiceNo]);

xTstSource is just a test table I created for the purpose of this post.

I have used the method that John puts forward and also used this one. This
one is simpler to implement and now is the one I use almost exclusively. I
stumbled across this solution in this forum and so must admit I am standing
on someone else's shoulders here. Thanks to all who answer here.

--
Jeffrey R. Bailey
"Greg Brady" <vze4qh8b@veriz on.net> wrote in message
news:U0sjc.8566 7$L31.63026@nwr ddc01.gnilink.n et...[color=blue]
> Is there a way to return a random sort order from a query?
>
>
>[/color]

[John Baker]

Re: Sort Order - random?

Jeffrey R. Bailey wrote:[color=blue]
> [cleaner solution snipped][/color]

Noticed something interesting when I tried this out - I included the
random field in my query results. The results were indeed in random
order, but they did not look like they were sorted by the random order
field either.

Not that this really matters, but could it be being called twice
somehow? Once when it generates the value for the field, and once when
it does the sort?

[Jeffrey R. Bailey]

Re: Sort Order - random?

Yes, that is exactly what is happening, and obviously it only needs to be
called once. My bad, I pasted the example to quickly. Let's try again:

SELECT xTstSource.Invo iceNo
FROM xTstSource
ORDER BY Rnd([InvoiceNo]);


--
Jeffrey R. Bailey
"John Baker" <baker-j@ix.netcom.com > wrote in message
news:U_vjc.2395 6$Vp5.5780@fe2. columbus.rr.com ...[color=blue]
> Jeffrey R. Bailey wrote:[color=green]
> > [cleaner solution snipped][/color]
>
> Noticed something interesting when I tried this out - I included the
> random field in my query results. The results were indeed in random
> order, but they did not look like they were sorted by the random order
> field either.
>
> Not that this really matters, but could it be being called twice
> somehow? Once when it generates the value for the field, and once when
> it does the sort?
>
>[/color]