Multiply the Random function by the record count. Convert it to a whole number. Put it in a loop that runs based on the number of random records you want. Use a 'gotorecord' function to go to the specified record number. Grab the ID number and append it to a temp table. Then run a query based on the linked temp and source tables.
Not easy, but it'd work.
If you have room in the record, add a Yes/No that says "Selected." Then when you want to do a selection, run an update query to reset the Selected flag. Then run a query based on the idea that you wanted X records out of Y total records.
Or you MIGHT wish to write some VBA code to do this because it would be more efficient...
Compute the fraction represeted by X/Y. For, say, 170 records out of 17000, this is 0.01 - but it could be anything.
Now for each record, run the RND function and see if the number returned is less than your fraction. If so, set the Selected flag.
When you are done, you should have APPROXIMATELY X records selected - but since we are dealing with random numbers, you'll have no guarantee of the exact number. Your range of records selected would probably be something like 170 records give or take 10.
Now, I might choose to do this a different way. If you are spot-checking your inventory to keep from having to do a serious wall-to-wall and ceiling-to-floor inventory of everything at once, I would just make a schedule of parts to be inventoried. Suppose your spot check is monthly. I would put every item on a list and assign a number from 1 to 12 based on - not a random but a very predictable number. Like, take the record number modulo 12 (+1, of course) to bring the entire inventory into play once per year.
I love the 'selected' check box--much better than my Temp Table idea. But I have to admit, I'm not a fan of your calculation method, as it would require accessing every single record and performing a calculation--meaning there would be 17000 calculations performed every time the script was run.
Perhaps a combination of the two? My calculation (which only runs x number of times, x being the number of records to be returned), and your 'selected' checkbox (which would eliminate the need to append to a secondary table).
Like this:
Code:
Dim num as integer
Dim num2, rcrdcount
Dim db
Dim rs
Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName")
rcrdcount = rs.RecordCount
num = Val(InputBox("Please select a count")) 'input the number of records
For i = 1 To num 'start loop based on the number of records inputted
num2 = Int((rcrdcount * Rnd) + 1) 'returns a record number between 1 and RecordCount
With rs
.MoveFirst 'go to first record
.Move num2 'move the random number of records forward
.Edit
!Selected= True 'mark as selected
.Update
End With
Next i
The above code inputs a number and automatically converts whatever's inputted to a whole number (thus, the 'dim num as integer), discarding any non-numerical characters. If the inputted number contains a non-numerical number to start (eg, 'sd123'), it returns '0'. There's a cap on the highest number an integer can be (32,767), so if you go over that, you'll have to change it to 'long'.
This code:
Code:
num2 = Int((rcrdcount * Rnd) + 1)
creates a random whole number between 1 and the total number of records available (again, capped by the 'int' type).
The For...next loop will loop based on the inputed number of records.
When run, it randomly updates the 'selected' checkbox in 'tablename' for the number of records required.
EDIT: I originally suggested using the 'gotorecord' method, but you of course can't do that with recordsets. Thus, the 'movefirst', 'move x' commands instead, which work fine.
tblRandom
----------
RandomID (Autonumber, Random (not increment))
YourMainTablePK (as FK ??"SKU"??)
1) Delete all records in tblRandom (if any)
2) Run an append query (to tblRandom), add all of the SKUs to the FK field - Access will assign a RandomID to each one.
3) Run a TOP X query on tblRandom, (sort on RandomID).
Join the TOP X query back to the main table if needed.
Jonathan, watch out for duplicated numbers so that you multi-select the same record more than once. Random numbers do not guarantee UNIQUELY random numbers.
John471 - your method is like mine, really, except that you are storing the random number. I don't need to. But you are right that it is less code.
