Random numbering of records used for sorting

[Jim Ory]

Using WinXP and Access2K2

Thanks to all that have helped me in the past.

I’ve checked the group for using the random number generator but haven’t
figured out how to use it in my situation.

The database holds a list of volunteers for a charity. Each day a report is
sent to a “phone†volunteer who takes calls from people that require a
service. The lists show which volunteers will provide a specific service on
certain days; i.e. meal delivery, visitation, transportation, etc.

Time to jump through hoops:

When the service is requested, the “Phone†volunteer checks the appropriate
list for a particular service and then proceeds to call another volunteer
that will perform the actual service.

The Charity’s request is to make the list random in the order on the report
and the order must change daily, so the first volunteer on the list on Monday
is not the first volunteer on the list on Tuesday, etc., etc. (This could
still happen even with random numbers, but not a major problem.

I have a text control (intSort) bound to a table (tblEntity) that I can
apply the random number to when a form (AddEditEntity) is “current†for that
volunteer. That is only good for one pass through the records, and that
method will not be tolerated or used by data entry persons..

To produce the “duty†list, I have a selection form and use the ‘query by
form’ method for each duty that can be provided, and that will generate the
duty ‘reports’.

What I’d like to do, but may be the wrong approach, is every time the
selection form is opened, it would produce another set of random numbers into
the table and the subsequent report would deal with a new set of sort
numbers. The developed report would be sorted by the new random sort number.

I thought also that using a recordset, I could apply the (Round(Rnd() * 999,
0) + 1) function to the [intSort] field in each record. But, I am not
knowledgeable enough to write that code.

Thanks for your help and/or suggestions..

 

[Jim Ory]

HI Steve,
Thanks for the suggestion. I was thinking along those lines, but had not
fully straightened it out in my head.

I made an update Query to run when the report form was opened and ran this:

UPDATE tblEntity SET tblEntity.intSort = Int((999*Rnd(Val([intEntityId])))+1)
WHERE (((tblEntity.ysnVolunteer)=True));

It uses the intEntityId to make every sort number different. It works very
well.

Many thanks for the solution advice.
--
Jim Ory

Hello Jim,

Try adding a field named "SortNumber" to your list of names. Then create and
Update query that updates SortNumber to a random number. Finally sort
SortNumber ascending. You will now have your list of names in a random
order.

Steve
(e-mail address removed)

Using WinXP and Access2K2

Thanks to all that have helped me in the past.

I've checked the group for using the random number generator but haven't
figured out how to use it in my situation.

The database holds a list of volunteers for a charity. Each day a report
is
sent to a "phone" volunteer who takes calls from people that require a
service. The lists show which volunteers will provide a specific service
on
certain days; i.e. meal delivery, visitation, transportation, etc.

Time to jump through hoops:

When the service is requested, the "Phone" volunteer checks the
appropriate
list for a particular service and then proceeds to call another volunteer
that will perform the actual service.

The Charity's request is to make the list random in the order on the
report
and the order must change daily, so the first volunteer on the list on
Monday
is not the first volunteer on the list on Tuesday, etc., etc. (This could
still happen even with random numbers, but not a major problem.

I have a text control (intSort) bound to a table (tblEntity) that I can
apply the random number to when a form (AddEditEntity) is "current" for
that
volunteer. That is only good for one pass through the records, and that
method will not be tolerated or used by data entry persons..

To produce the "duty" list, I have a selection form and use the 'query by
form' method for each duty that can be provided, and that will generate
the
duty 'reports'.

What I'd like to do, but may be the wrong approach, is every time the
selection form is opened, it would produce another set of random numbers
into
the table and the subsequent report would deal with a new set of sort
numbers. The developed report would be sorted by the new random sort
number.

I thought also that using a recordset, I could apply the (Round(Rnd() *
999,
0) + 1) function to the [intSort] field in each record. But, I am not
knowledgeable enough to write that code.

Thanks for your help and/or suggestions..

 

[Grimwadec]

G'day Jim/Steve

Thought I'd use your code for producing a golf draw. I created a table with
fields:Surname(Text), intEntityID (Primary Key and AutoNumber), intSort
(Number, Long Integer)
Using your UPDATE query but substituting 10 for your 999 (I only have 10
names in my trial) and leaving out the WHERE clause which I don't need, it
does not generate UNIQUE numbers to the intSort field. For example it
generated 5,3,2,2,7,5,5,8,4,7. Now of course if you leave your 999 in the
code you will probably but not necessarily get unique numbers but I suspect
as the gap between the number of entities in the table and 999 narrows the
chances of non unique numbers increases. Any ideas on ensuring the numbers
are unique?
--
Grimwadec

HI Steve,
Thanks for the suggestion. I was thinking along those lines, but had not
fully straightened it out in my head.

I made an update Query to run when the report form was opened and ran this:

UPDATE tblEntity SET tblEntity.intSort = Int((999*Rnd(Val([intEntityId])))+1)
WHERE (((tblEntity.ysnVolunteer)=True));

It uses the intEntityId to make every sort number different. It works very
well.

Many thanks for the solution advice.
--
Jim Ory

Hello Jim,

Try adding a field named "SortNumber" to your list of names. Then create and
Update query that updates SortNumber to a random number. Finally sort
SortNumber ascending. You will now have your list of names in a random
order.

Steve
(e-mail address removed)

Using WinXP and Access2K2

Thanks to all that have helped me in the past.

I've checked the group for using the random number generator but haven't
figured out how to use it in my situation.

The database holds a list of volunteers for a charity. Each day a report
is
sent to a "phone" volunteer who takes calls from people that require a
service. The lists show which volunteers will provide a specific service
on
certain days; i.e. meal delivery, visitation, transportation, etc.

Time to jump through hoops:

When the service is requested, the "Phone" volunteer checks the
appropriate
list for a particular service and then proceeds to call another volunteer
that will perform the actual service.

The Charity's request is to make the list random in the order on the
report
and the order must change daily, so the first volunteer on the list on
Monday
is not the first volunteer on the list on Tuesday, etc., etc. (This could
still happen even with random numbers, but not a major problem.

I have a text control (intSort) bound to a table (tblEntity) that I can
apply the random number to when a form (AddEditEntity) is "current" for
that
volunteer. That is only good for one pass through the records, and that
method will not be tolerated or used by data entry persons..

To produce the "duty" list, I have a selection form and use the 'query by
form' method for each duty that can be provided, and that will generate
the
duty 'reports'.

What I'd like to do, but may be the wrong approach, is every time the
selection form is opened, it would produce another set of random numbers
into
the table and the subsequent report would deal with a new set of sort
numbers. The developed report would be sorted by the new random sort
number.

I thought also that using a recordset, I could apply the (Round(Rnd() *
999,
0) + 1) function to the [intSort] field in each record. But, I am not
knowledgeable enough to write that code.

Thanks for your help and/or suggestions..

 

[John W. Vinson]

Any ideas on ensuring the numbers
are unique?

Shuffle a set of unique numbers, instead of trying to generate them. Create a
table Num with one numeric field N, with values from 1 to the maximum you'll
ever need. Then 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 a query based on Num by typing

Shuffle: RndNum([N])

in a vacant Field cell - 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.