Real 10 random records

  • Thread starter Thread starter Ljudmil
  • Start date Start date
L

Ljudmil

Hi,
I have a query which gives me 10 random records.

SELECT TOP 10 Basis_.t, Basis_.r, Basis_.Basis_code
FROM Basis_
ORDER BY Rnd(Basis_code);

Basis_code is an Auto Number field.
It works fine but when I open the database and run the query I always get
the same “10 random recordsâ€. What should I do to get different 10 random
records when I open the database?
Thanks in advance!
Ljudmil
 
See http://support.microsoft.com/default.aspx?id=208855

Possible, not quite as easy as it ought to be.

Copy and paste this little function into a module; save the module as
basRandom (anything except RndNum, you can't use the same name twice);


Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndNum([somefield])

where "somefield" is any numeric field in your table - this just forces Access
to give you a new random number for every row. If you don't have a numeric
field available then you can use RndNum(Len([SomeField])) to force a number to
be generated.

Sort by this field and it will shuffle your data into random order.

Source: John Vinson

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
On Fri, 11 Dec 2009 04:28:01 -0800, Ljudmil

You first call the Randomize function. Check the details in the Help
file.

-Tom.
Microsoft Access MVP
 
Assuming that you have more than 10 records ;-)

ORDER BY Rnd(IsNull([Basis_code])*0+1)

Try the above Order By. It seeds the random number. For some reason beyond
me, the IsNull helps even though Basis_Code is an autonumber. OH! Is it also
a primary key or at least a unique index? It's possible to repeat even in
autonumber fields if not especially if appending records to the table from
other sources.
 
It works fine
Thank you very much

John Spencer said:
See http://support.microsoft.com/default.aspx?id=208855

Possible, not quite as easy as it ought to be.

Copy and paste this little function into a module; save the module as
basRandom (anything except RndNum, you can't use the same name twice);


Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndNum([somefield])

where "somefield" is any numeric field in your table - this just forces Access
to give you a new random number for every row. If you don't have a numeric
field available then you can use RndNum(Len([SomeField])) to force a number to
be generated.

Sort by this field and it will shuffle your data into random order.

Source: John Vinson

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi,
I have a query which gives me 10 random records.

SELECT TOP 10 Basis_.t, Basis_.r, Basis_.Basis_code
FROM Basis_
ORDER BY Rnd(Basis_code);

Basis_code is an Auto Number field.
It works fine but when I open the database and run the query I always get
the same “10 random recordsâ€. What should I do to get different 10 random
records when I open the database?
Thanks in advance!
Ljudmil
Click to expand...
.
Click to expand...
 
Hi
could you please help, I am new to Access

I have 60 Brands and each Brand have more than 2K customers

I am trying to pick 40 customer from each brand randomly, and I have failed

fields in the table

Customer #
email
Brand ID

You help is much appreciated

thanks
 
Hi
could you please help, I am new to Access

I have 60 Brands and each Brand have more than 2K customers

I am trying to pick 40 customer from each brand randomly, and I have failed

fields in the table

Customer #
email
Brand ID

You help is much appreciated

thanks
Click to expand...

You can use the Top Values property of a subquery, 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 use a Query

SELECT [Customer #], , [Brand ID]
FROM table
WHERE [Customer #] IN
(SELECT TOP 40 X.[Customer #] FROM table AS X
WHERE X.[Brand ID] = table.[Brand ID]
ORDER BY RndNum([Customer #]);

Untested air code, post back if you have problems with it!
 
Back
Top