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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 # Brand ID You help is much appreciated thanks |
#8
|
|||
|
|||
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 # 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 | |
|
|