Filter ms-Access records based on two criteria (1 Viewer)

[Bushra Eisa]

this is my first timeusing Microsoft Access and i'm having diffuclties completeling a task at my internship. I was given a list of records and i'm supposed to create a query that picks random records (winners) based on the country and the year of choice. I created a form with two dropdown lists for the countries and the years and a textbox where the user would select the number of winners.
I was only able to do the randomizing bit by implementing the following SQL code:

     `SELECT TOP 10 Sheet2.name, Sheet2.country, Sheet2.Year
      FROM Sheet2
      ORDER BY Rnd(-(100000*ID)*Time());

My problem now is that I want to choose the number of winners and select the country from dropdown list in my form and the year and filter the records based on the selection.
Hope it made sense.
Any help would be appreciated!

 

[Ranman256]

get the recordset of your choices to filter the query:
set rst = currentdb.openrecordset("select * from table...)
iTop = rst.recordcount

get your N random winners:

for i = 1 to N
    iWin = Int((iTop * Rnd) + 1)   
  
  'mark iWin as chosen by updating a field on the record
next

 

[Ranman256]

'pick N random records by grabbing the index and saving it in the
collection. The collection prevents duplicates.


'N = get N records
'iCount = total count of records in the recordset

dim coll as new collection

on error resume next
set rst = currentdb.openrecordset("select * from table where...)
iCount = rst.recordcount

While coll.Count < N
    iRnd = Int(iCount * Rnd + 1)
    
    rst.movefirst
    rst.Move iRnd - 1   'goto the random record
    iKey = rst.fields("ID").value   'get the ID of the random record.

    coll.add iKey,iKey
wend

'get all saved records
for i = coll.count
   msgbox coll(i)
next

 

[Bushra Eisa]

I'm sorry could you elaborate more on the code and where to change/use it?

 

[Ranman256]

your rst is created from the sql you will use to filter the records you want.

assign N to the number of random picks you want.
N = 5 'to get 5 random people

the code will then get 5 random keys from the recordset (rst)
the key is the ID of the person.
this key is put into a collection, which is a list that wont allow duplicates: coll.add key,key
because its possible to get the same random # more than once.

once 5 keys have been captured,
put these keys into a table and run a query against it to pull only those 'winners'.

for i = 1 to coll.count