A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Real 10 random records



 
 
Thread Tools Display Modes
  #1  
Old December 11th, 2009, 01:28 PM posted to microsoft.public.access.queries
Ljudmil
external usenet poster
 
Posts: 6
Default Real 10 random records

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

  #2  
Old December 11th, 2009, 03:03 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Real 10 random records

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

Ljudmil wrote:
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

  #3  
Old December 11th, 2009, 03:07 PM posted to microsoft.public.access.queries
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Real 10 random records

On Fri, 11 Dec 2009 04:28:01 -0800, Ljudmil
wrote:

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

-Tom.
Microsoft Access MVP


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

  #4  
Old December 11th, 2009, 03:25 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default Real 10 random records

Is the field a text field or number field?
--
Milton Purdy
ACCESS
State of Arkansas


"Ljudmil" wrote:

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

  #5  
Old December 11th, 2009, 04:01 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Real 10 random records

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Ljudmil" wrote:

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

  #6  
Old December 11th, 2009, 04:05 PM posted to microsoft.public.access.queries
Ljudmil
external usenet poster
 
Posts: 6
Default Real 10 random records


It works fine
Thank you very much

"John Spencer" wrote:

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

Ljudmil wrote:
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

.

  #7  
Old May 12th, 2010, 10:08 PM posted to microsoft.public.access.queries
MIA
external usenet poster
 
Posts: 51
Default Real 10 random records

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

  #8  
Old May 12th, 2010, 10:35 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Real 10 random records

On Wed, 12 May 2010 14:08:01 -0700, Mia wrote:

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 #], [email], [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!
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.