want to get field value from say the 10th record of a query - how?

[Helen]

I need to make a random selection from a master list based on criteria, so
some entries in the master won't fit the criteria.

First, I narrow down the master to those entries that DO fit, then I need to
select from that narrowed list randomly.

What I WANT to do, is to count the entries that fit the criteria, generate a
random number RND, between 1 and COUNT, and choose the RNDth record, but I
don't know how. I know how to get the first, last, average, .... but not the
3rd, 10th, 125th etc.

Thanks for being here to put questions to!
Helen

 

[ChrisO]

I must not be understanding the requirement here. I think what if you had a
random list then the 3, 10, 125 records would not be any more or less random
than records 1, 2, 3. Maybe all you need to do is sort on the random numbers
and select the Top 3.

 

[kc-mass]

Hi Helen,

One way you can do that is to select the records that match the criteria
into a recordset.
Determine how many records are there and use rnd to pick the data positions
in the recordset.
Then simply use movenext multiple times to position the recordset onto the
randomly selected records. You can copy them to a new table and you have
yourself a random recordset.

Not elegant but it works.

Regards

Kevin

 

[Helen]

I think you did misunderstand.
Say the MASTER has
ID type size
1 A large
2 A small
3 B large
4 B small
but I want a random selection only from "type = A", or from "size = large"
I need to narrow down the list from the master to ONLY A, or ONLY large,
BEFORE I make the random selection.

 

[Helen]

OK - yes, that's one way, and yes, it's a bit ugly.

I was hoping for something like:
qryFitCriteria (my query with the records that fit the criteria)
intCount = DCount the number of records in the query
intRandom = random number between 1 and intCount

then I thought/hoped there'd be a function/command I was yet unaware of like:

docmd.gotorecord(intRandom,qryFitCriteria) or
for gotorecord(<number of the record in the query>,<query or table name>)

DLookup(ID,qryFitCriteria,intRandom)
for DLookup(<fieldname>,<query or table name>,<record number>)

I know the above two don't DO that, but.....

Looks like I might have to write my own little procedure to do it, based on
your suggestion - THANKS for that idea!

 

[PieterLinden via AccessMonster.com]

like this maybe:
http://www.mvps.org/access/queries/qry0011.htm

 

[Helen]

Thanks for that idea, but I think there's a problem:

I could potentially have a narrowed list of up to 1000 records (from a
master of about 5000), and I want just ONE record chosen at random out of
that 1000 to fit a set of criteria. There might be 20-odd 'sets' of criteria
to attend to, so 20 random selections, each being a single random selection
from a different narrowed list.

The method you linked to is going to randomly choose between just 0 and 1,
that is, 50/50 chance of selecting the 'next' record, so picking a single
record that way from a list of ~1000, I'd always tend to get something from
early on in the list, given that it would start from the top, nevertheless,
it's given me the seeds of how to go about creating my own function.

Thanks for the input though.

 

[kc-mass]

Hi Helen

There is no concept of record number in Access. What the suggested
method does is apply the "random number" to the ordinal position of the
data.

As a thought, what if you made your criteria query into a make table query
with
an autonumber ID; ran the rnd function against it; built an IN() clause with
the
results and selected your records with the "IN" function as your "WHERE"
clause.

I also see that there is a Move(x) function so even the original idea can be
simplified.

Regards

Kevin

 

[Helen]

Kevin
I've done one version - the trifling one (a single criterion) this way:
The master already has an ID auto-number.
1: find MIN masterID
2: find MAX masterID
3: find RND between them
4: find LowerID = max of masterID (for records that fit criterion) where ID
<= RND
5: find Higher ID = min of masterID (for records that fit) where ID >= RND
6: use whichever is closer to RND

It's MUCH faster than the previous awkward way I was doing it before.

I'm now working on speeding up the more nuggety version - up to 20 selection
criteria, could have any or all required to be satisfied, but the same idea
should work.

Currently the longer one takes almost a minute to spit out results, that's
why I want something faster!

I'll post my timing results - when I get it done.

 

[John W. Vinson]

Kevin
I've done one version - the trifling one (a single criterion) this way:
The master already has an ID auto-number.
1: find MIN masterID
2: find MAX masterID
3: find RND between them
4: find LowerID = max of masterID (for records that fit criterion) where ID
<= RND
5: find Higher ID = min of masterID (for records that fit) where ID >= RND
6: use whichever is closer to RND

It's MUCH faster than the previous awkward way I was doing it before.

I'm now working on speeding up the more nuggety version - up to 20 selection
criteria, could have any or all required to be satisfied, but the same idea
should work.

Currently the longer one takes almost a minute to spit out results, that's
why I want something faster!

I'll post my timing results - when I get it done.

You can use the Top Values property of a query, 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 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.

 

[JimBurke via AccessMonster.com]

Seems to me this should work, and it shouldn't take that long (I haven't
tested this, but the logic seems to fit what you're trying to accomplish,
unless I'm misinterpreting something):

Open a recordset based on your query with all the fields you need, sorted by
masterID.

Generate your random number with the lower range of 1 and the upper range
of recordset.RecordCount - this will give you a random record # from
your query results, e.g.if there are 300 records that meet your criteria,
then this
should generate a random number between 1 and 300. I think that you have
to do a MoveLast
beofre the count is available.

Do a MoveFirst to make sure you're back at the beginning of the recordset.
Use the recordset Move function and move 'n-1' records, where n is your
random number
from step 2. This should bring you to your random record. If the random
number is 1 you
won't need to do a move, since you'll already be at record 1.

Here's a link that shows how to generate a random number in a given range:

http://office.microsoft.com/en-us/access/HA012289011033.aspx

 

[ChrisO]

' Add a 'SortOrder' field to the table (Single)

Private Sub cmdRand_Click()
Dim lngTop As Long
Dim strType As String
Dim strSize As String
Dim strSQL As String

lngTop = 10
strType = "B"
strSize = "Small"
strSQL = " SELECT TOP " & CStr(lngTop) & " *" & _
" FROM tblMaster" & _
" WHERE [Type] = '" & strType & "' And [Size] = '" & strSize &
"'" & _
" ORDER BY SortOrder"

' Randomize the SortOrder field in the table.
CurrentDb.Execute "UPDATE tblMaster SET SortOrder = 1000000 *
Rnd([ID])", 128

' Apply it to a sub form.
Me.ctlSub.Form.RecordSource = strSQL

' Or open a recordset.
With CurrentDb.OpenRecordset(strSQL)
Do Until .EOF
' Do
' your
' thing
.MoveNext
Loop
.Close
End With

End Sub

 

[Helen]

Looks likes I've piqued some interest in solving my problem, but STOP
SUGGESTING NOW. Although,.......... the attention has been kind of nice

I've done the big one - test run done on 10 records.
Each record needs a random selection from the master chosen for it, with a
different set of 20 criteria for each of the 10.
The old (and hideously ugly) way, took 90 seconds.

The new way? About 8 seconds!
Used the same idea as the short way I posted earlier.

Thanks for all the input!!