How can i make access allocate random records?

[sirantonycartwr]

Hiya,

Im going to make a database which has a table of different talks (or presentations) and a table of staff. One staff can have many talks. One talk can only have one staff.

When I press a button on the form, I want Access to allocate the talks randomly to the staff.

So, for example, lets say we have three staff members:
staff1, staff2 and staff3.

and we have 10 talks:
talk1, talk2, talk3, talk4, etc, etc.

I want this button to randomly allocate the talks to the staff, so, staff1 may have 4 talks, staff2 may have 3 talks and staff3 may have 3 talks. Or staff1 may have 5 talk, staff2 may have 2 talks and staff3 may have 3 talks.

Can this be done?
Thanks, Ant...

 

[tehNellie]

  Randomize
  sngRrndno = Rnd()
  'count the records in the table
  lngrecords = DCount("column", "tablename")
  'select a random id number
  intRandomrow = ((lngrecords) * sngRndno)

I dug this out of a password generator I wrote ages ago so there is probably a much more elegant way of doing this.

Randomize and rnd() use the inbuilt random number generator which is less than 1 which is then multiplied by the number of rows to pick a random id number to go into a SELECT query. On small numbers of rows it isn't that great at properly randomising, but witha word list of 30K + rows for the pw generator it seems to work ok.