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
|
|||
|
|||
select random records problem
I use a query to select 5 random records from my db (1285 records in total)
the fields are, Id - first name - surname - dept I enter Rnd([id]) in a blank field on my query, set the total to 5, set the sort order asc and run the query. Works great - every time I run the query I get 5 different members of staff - which is exactly what I want. This is used as a monthly draw to award staff a prize The problem is, every time the database is closed and reopened, it picks the exact same members of staff , as though in a loop - if the database is left open it selects 5 different every time. what am I doing wrong, and more to the point how can I prevent the same selection being picked every time?? any help would be appreciated Using Access 97 Steve |
#2
|
|||
|
|||
select random records problem
I never had an ongoing random pick to do so never run into this problem but I
have a possible solution. Add a Picked date to your table. Pre-load with a default date one day older than the X below. In an update query use Rnd([id]&[Picked]) with criteria of Date() - X where the x represents how long ago for them to be eligble again. Update the Picked field with Date(). Then run select query for those picked today. -- Build a little, test a little. "steve goodrich" wrote: I use a query to select 5 random records from my db (1285 records in total) the fields are, Id - first name - surname - dept I enter Rnd([id]) in a blank field on my query, set the total to 5, set the sort order asc and run the query. Works great - every time I run the query I get 5 different members of staff - which is exactly what I want. This is used as a monthly draw to award staff a prize The problem is, every time the database is closed and reopened, it picks the exact same members of staff , as though in a loop - if the database is left open it selects 5 different every time. what am I doing wrong, and more to the point how can I prevent the same selection being picked every time?? any help would be appreciated Using Access 97 Steve |
#3
|
|||
|
|||
select random records problem
I never had an ongoing random pick to do so never run into this problem but I
have a possible solution. Add a Picked date to your table. Pre-load with a default date one day older than the X below. In an update query use Rnd([id]&[Picked]) with criteria of Date() - X where the x represents how long ago for them to be eligble again. Update the Picked field with Date(). Then run select query for those picked today. -- Build a little, test a little. "steve goodrich" wrote: I use a query to select 5 random records from my db (1285 records in total) the fields are, Id - first name - surname - dept I enter Rnd([id]) in a blank field on my query, set the total to 5, set the sort order asc and run the query. Works great - every time I run the query I get 5 different members of staff - which is exactly what I want. This is used as a monthly draw to award staff a prize The problem is, every time the database is closed and reopened, it picks the exact same members of staff , as though in a loop - if the database is left open it selects 5 different every time. what am I doing wrong, and more to the point how can I prevent the same selection being picked every time?? any help would be appreciated Using Access 97 Steve |
#4
|
|||
|
|||
select random records problem
I'm snowed ...
My knee jerk reaction is that you are sorting exactly the same each time you start from scratch. You aren't randomizing anything. But that's only a part of my problem. Where is the "total" to which you refer? If that's the property on how many records to return then it explains how it continues to work just fine between starts. When you run the query it returns 5 records, the next time you tell it to run it just gives you the next 3 records - it does NOT re-run the query. The way to get done what you want is to create a function in a regular module to randomize on a seed you pass in. That function has test first that a local static the flag is not set. If Not set then Set it and calculate and return the value. If it is set just return with the old value still in play. The local flag has to be static so that it retains its state. Ergo, this calculation is performed only once per execution of the query. You would choose elements to randomize and compare in the query such that you'd get the desired results. RandomNumber: RandomizeNumber(YourArgument) Somewhere in the archives here I'm sure I still have an example of that code and a query that used it (back in Access 1 or 2!). If you need it in a hurry you might google on combinations of "randomize" "Joe Foster" "Access" "Dev Ashish" If you're unable to find it get back to me and I'll search here. HTH -- -Larry- -- "steve goodrich" wrote in message ... I use a query to select 5 random records from my db (1285 records in total) the fields are, Id - first name - surname - dept I enter Rnd([id]) in a blank field on my query, set the total to 5, set the sort order asc and run the query. Works great - every time I run the query I get 5 different members of staff - which is exactly what I want. This is used as a monthly draw to award staff a prize The problem is, every time the database is closed and reopened, it picks the exact same members of staff , as though in a loop - if the database is left open it selects 5 different every time. what am I doing wrong, and more to the point how can I prevent the same selection being picked every time?? any help would be appreciated Using Access 97 Steve |
#5
|
|||
|
|||
select random records problem
Ooops. Should have read ... next 5 records.
-- -Larry- -- "Larry Daugherty" wrote in message ... I'm snowed ... My knee jerk reaction is that you are sorting exactly the same each time you start from scratch. You aren't randomizing anything. But that's only a part of my problem. Where is the "total" to which you refer? If that's the property on how many records to return then it explains how it continues to work just fine between starts. When you run the query it returns 5 records, the next time you tell it to run it just gives you the next 3 records - it does NOT re-run the query. The way to get done what you want is to create a function in a regular module to randomize on a seed you pass in. That function has test first that a local static the flag is not set. If Not set then Set it and calculate and return the value. If it is set just return with the old value still in play. The local flag has to be static so that it retains its state. Ergo, this calculation is performed only once per execution of the query. You would choose elements to randomize and compare in the query such that you'd get the desired results. RandomNumber: RandomizeNumber(YourArgument) Somewhere in the archives here I'm sure I still have an example of that code and a query that used it (back in Access 1 or 2!). If you need it in a hurry you might google on combinations of "randomize" "Joe Foster" "Access" "Dev Ashish" If you're unable to find it get back to me and I'll search here. HTH -- -Larry- -- "steve goodrich" wrote in message ... I use a query to select 5 random records from my db (1285 records in total) the fields are, Id - first name - surname - dept I enter Rnd([id]) in a blank field on my query, set the total to 5, set the sort order asc and run the query. Works great - every time I run the query I get 5 different members of staff - which is exactly what I want. This is used as a monthly draw to award staff a prize The problem is, every time the database is closed and reopened, it picks the exact same members of staff , as though in a loop - if the database is left open it selects 5 different every time. what am I doing wrong, and more to the point how can I prevent the same selection being picked every time?? any help would be appreciated Using Access 97 Steve |
#6
|
|||
|
|||
select random records problem
You need to initialize the random or you will get the same exact set of
random numbers every time. From a posting by John Vinson 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 ' this generates a new start to the random sequence 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 '================================================= === steve goodrich wrote: I use a query to select 5 random records from my db (1285 records in total) the fields are, Id - first name - surname - dept I enter Rnd([id]) in a blank field on my query, set the total to 5, set the sort order asc and run the query. Works great - every time I run the query I get 5 different members of staff - which is exactly what I want. This is used as a monthly draw to award staff a prize The problem is, every time the database is closed and reopened, it picks the exact same members of staff , as though in a loop - if the database is left open it selects 5 different every time. what am I doing wrong, and more to the point how can I prevent the same selection being picked every time?? any help would be appreciated Using Access 97 Steve |
#7
|
|||
|
|||
select random records problem
On Thu, 16 Jul 2009 20:52:41 +0100, "steve goodrich"
wrote: I use a query to select 5 random records from my db (1285 records in total) the fields are, Id - first name - surname - dept I enter Rnd([id]) in a blank field on my query, set the total to 5, set the sort order asc and run the query. Works great - every time I run the query I get 5 different members of staff - which is exactly what I want. This is used as a monthly draw to award staff a prize The problem is, every time the database is closed and reopened, it picks the exact same members of staff , as though in a loop - if the database is left open it selects 5 different every time. what am I doing wrong, and more to the point how can I prevent the same selection being picked every time?? any help would be appreciated Using Access 97 Steve You can use the Top Values property of a query, with help from a little VBA to rescramble the random numbers every time it runs. 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] |
#8
|
|||
|
|||
select random records problem
Thank you so much John - It works perfectly.
Steve "John Spencer" wrote in message ... You need to initialize the random or you will get the same exact set of random numbers every time. From a posting by John Vinson 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 ' this generates a new start to the random sequence 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 '================================================= === steve goodrich wrote: I use a query to select 5 random records from my db (1285 records in total) the fields are, Id - first name - surname - dept I enter Rnd([id]) in a blank field on my query, set the total to 5, set the sort order asc and run the query. Works great - every time I run the query I get 5 different members of staff - which is exactly what I want. This is used as a monthly draw to award staff a prize The problem is, every time the database is closed and reopened, it picks the exact same members of staff , as though in a loop - if the database is left open it selects 5 different every time. what am I doing wrong, and more to the point how can I prevent the same selection being picked every time?? any help would be appreciated Using Access 97 Steve |
Thread Tools | |
Display Modes | |
|
|