Hello
Can anyone tell me which is the best method (or the code) for randomizing database records? The selected record, after randomization, is to be placed in a text box. I am using VB6 and Access 97.
Thanks
Printable View
Hello
Can anyone tell me which is the best method (or the code) for randomizing database records? The selected record, after randomization, is to be placed in a text box. I am using VB6 and Access 97.
Thanks
Is there a field (hopefully numeric field) that could be used as a key in randomizing?
Yes the Primary key id is numeric
Thanks
One possible solution: If you want to return complete recordset or a subset, query the DB returning the record ids. Size a Long array of the recordcount and populate the array with the record ids. Then pass the array to a routine similar to following. The array will be randomized when it returns. The array are your randomized record ids, so to return one from the db, either query on the value in the array or if an existing recordset was maintained, use .Find to locate the random record
Code:Private Function ShuffleExistingArray(inArray() As Long) As Boolean
' inArray must be a populated 1D array. Any LBound can be used
Dim X As Long, rndNr As Long
Dim tmpArray() As Long
Dim lOffset As Long, nrItems As Long
On Error GoTo EH
tmpArray = inArray ' copy array structure
lOffset = LBound(tmpArray) ' get its LBound
nrItems = Abs(UBound(tmpArray) - lOffset) ' calc nr of array items in ref to 0-LBound
For X = nrItems To 0 Step -1 ' begin randomly filling output array
rndNr = Int(Rnd * (X + 1)) + lOffset ' get rnd Index from temp array
inArray(X + lOffset) = tmpArray(rndNr) ' add to output array
tmpArray(rndNr) = tmpArray(X + lOffset) ' swap last array Index with selected array Index
Next
ShuffleExistingArray = True ' done
EH:
If Err Then
MsgBox Err.Description, vbOKOnly
Err.Clear
inArray = tmpArray
End If
End Function
If an error occurs you'd return an array with duplicated items... just select random array elements and swap them within same array.Quote:
Originally Posted by LaVolpe
Thanx for the tip. Simply removing the line: inArray = tmpArray from the EH: section would suffice. The only error would be lack of memory to build the tmpArray, which if it happens would occur before loop starts and the inArray in the EH section should not be allowed to be modified then.
Please refer to post #24 by Logophobic to minimize bias in randomization http://www.vbforums.com/showthread.p...andomize+array
Firstly, you don't need to use a second array to suffle a given array.
That will be inefficient with a large array. To suffle an array I will do this:
Secondly, no need to build a random array to get a random record from a table. This is my way:Code:Function SuffleArray(ByRef inArray As Variant) As Boolean
Dim i As Long
Dim j As Long
Dim iMin As Long
Dim iMax As Long
Dim vTemp As Variant
If IsArray(inArray) Then
iMin = LBound(inArray)
iMax = UBound(inArray)
Randomize
For i = iMin To iMax
j = Int((iMax - iMin + 1) * Rnd()) + iMin
vTemp = inArray(i)
inArray(i) = inArray(j)
inArray(j) = vTemp
Next
SuffleArray = True
End If
End Function
- Open a DAO or ADO recordset : rst)
- Get RecordCount of rst : n -- use rst.MoveLast if required
- To get a random record:
- Offset = Int(n * Rnd()) -- between 0 and n-1
- rst.MoveFirst
- rst.Move Offset
- Read record data
anhn, I like your tweaks to the shuffle routine. The array method allows entire "list" to be shuffled without repeating. The second solution allows potential of repeating records. Depending on user's needs, they have choices.
Another method: Randomly sort the recordset.
Open a DAO or ADO recordset based on the below SQL:
SQL = "SELECT Table1.*, FROM Table1 ORDER BY Rnd()"
Now you can get data of one record at a time from the top to the bottom of the recordset that is already in a random order.
Edit: This method may not work because Rnd() has the same value for all records. Need to tweak some more.
Edit2: It may work if we pass RecordID to Rnd():
SQL = "SELECT Table1.*, FROM Table1 ORDER BY Rnd(Table1.RecordID)"
Behaviour of Rnd() or similar database function is database dependent... it won't work with access cause same value is returned for all records while seeding introduces bias.
I think bias is not a big deal in this case. I just thought a way like that.Quote:
Originally Posted by leinad31
It's good if someone input some tweaks to make it works as simple as possible.
In retrospect, he wasn't very clear of his requirements. Such as if repetitive selection is allowed, or single selection until all numbers are exhausted, or how many random samples he will be retrieving from the population, etc.
It would be best to wait for his response and refrain for the time being from lengthening this thread with more code/posts.
Hello again.
Thank you for your replies.
Leinad31 asked for some further information regarding the purpose of the query. We want to be able, from time to time, to select between one and five of our customers for investigative purposes. It doesn't matter if the same customer is selected again at a later date.
RAND() in MS SQL will never work in a SELECT statement - notice this
It returnsCode:select rand(),* from ledtype_t
The RAND() is evaluated once prior to the building of the working resultset. Each row gets the exact same value.Code:LedType LedTypeDesc Hierarchy TDate
---------------------- ------- ---------------- ------------------------------ -----------------------
0.204834390892421 AP Accounts Payable NULL
0.204834390892421 BB Budget Book D NULL
0.204834390892421 BP Budget Priors G NULL
0.204834390892421 BR Revised Budget F NULL
0.204834390892421 BT Budget Transfer F NULL
0.204834390892421 BU Beginning Budget G NULL
0.204834390892421 GP G/L Priors H NULL
0.204834390892421 HC Hand Checks L NULL
0.204834390892421 IN Invoices L NULL
0.204834390892421 JE Journal Entry J NULL
0.204834390892421 LQ Liquidation L NULL
0.204834390892421 OC Other Charges M NULL
0.204834390892421 OR Other Receipts R NULL
0.204834390892421 PC Payroll Charges N NULL
0.204834390892421 PO Purchase Order V NULL
0.204834390892421 PP Previous PO V NULL
0.204834390892421 PX Adjust PO V NULL
0.204834390892421 RQ Requisition W NULL
0.204834390892421 TH Threshold D NULL
0.204834390892421 VD Void Check/Inv NULL
(20 row(s) affected)
I've seen suggestions to place RAND() into a USER DEFINED FUNCTION with MS SQL - I think by doing that it gets evaluated row by row.
@szlamany, The same thing happens in Access as said in Post#10. It also won't work with a UDF.
Quote:
Originally Posted by anhn
If there are no gaps in the sequence of your primary key then get "SELECT MIN(pk), MAX(pk) FROM yourTable" first, then generate 3-5 random numbers into a CSV using VB Rnd(), and finally "SELECT columns FROM yourTable WHERE pk IN (" & csv & ") " to get random records.Quote:
Originally Posted by Bomber2006
If there are gaps in the pk series (possibility that random number generated is a non-existent key) then you can simply retrieve more than you'll normally need, e.g. 10 records, and get your 3-5 records from that set.
@anhn - apparently you cannot put RAND() into a UDF - it's non determistic so it's not valid.
But I guess you can put it in a VIEW and then reference the VIEW in the UDF - here's a link that discusses that
http://www.vbforums.com/showthread.p...14#post2148414
But of course this is all for MS SQL...
A concept you can use for any database would be:
1) Determine the number of rows in the table - SELECT COUNT(*) will do that
2) Use RND() from the client side - with the number of row value - to determine which record you want
3) Do a query something like this to get that one record
The sub-query gets only the number of rows determined in step 2 to offer up the random row of choice.Code:Select Top 1 *
From (Select Top {TheRandomRowFoundinStep2} *
From SomeTable Order by PriKey Desc) "XYZ"
Order by PRIKEY Desc will put the "last" record in that sequence first.
Select Top 1 will get just that record.
You can probably do something in the sub-query to get just the PRIKEY values and select WHERE PRIKEY=(...) that - lots of ways to handle this.