Prev: Query 90 expiration
Next: sumif function in Access query, possible?
From: Rascaltoo on 26 Apr 2010 11:51
I have a webpage that runs a query. Presently I have the top 15 entries
always at the top, and any others beneath them. I need to rotate the 15 on
top as well as the rest underneath. I know that it is probably a simple sort
command but I'm stuck. Anyone able to help me?
From: ghetto_banjo on 26 Apr 2010 12:04
your query would need to include a field that is a random number. you
can do this with the Rnd() function. It will work best if you have
some sort of numeric ID field for your primary key that you can
reference. Something like: Int( Rnd([IDNumber]) * 1000 ) would give
a random number between 0-999 I believe. Then you could sort on this
field in the query and thus sorting randomly each time the query is
run. More info here:

http://www.techonthenet.com/access/functions/numeric/rnd.php


From: Jerry Whittle on 26 Apr 2010 12:18
SELECT TOP 15 *
FROM YourTable
ORDER BY Rnd(IsNull(YourField)*0+1);

SELECT *
FROM YourTable
ORDER BY Rnd(IsNull(YourField)*0+1);

Plug in good names for YourTable and YourField. It would be nice if
YourField was the primary key field for that table.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Rascaltoo" wrote:

> I have a webpage that runs a query. Presently I have the top 15 entries
> always at the top, and any others beneath them. I need to rotate the 15 on
> top as well as the rest underneath. I know that it is probably a simple sort
> command but I'm stuck. Anyone able to help me?
From: John W. Vinson on 26 Apr 2010 12:41
On Mon, 26 Apr 2010 08:51:01 -0700, Rascaltoo
<Rascaltoo(a)discussions.microsoft.com> wrote:

>I have a webpage that runs a query. Presently I have the top 15 entries
>always at the top, and any others beneath them. I need to rotate the 15 on
>top as well as the rest underneath. I know that it is probably a simple sort
>command but I'm stuck. Anyone able to help me?

You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Query 90 expiration
Next: sumif function in Access query, possible?