Real 10 random records

[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

 

[John Spencer]

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

 

[Tom van Stiphout]

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

 

[golfinray]

Is the field a text field or number field?

 

[Jerry Whittle]

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.

 

[Ljudmil]

It works fine
Thank you very much

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

.

 

[Mia]

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

 

[John W. Vinson]

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

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!