create a database that randomize names into 17 groups of 10

[leo002000]

Hi,

I would like to be able to randomize student names into groups of 10, from a simple text file import. The text file would have the full name of the student and would be imported into access. I would then like to randomize those students (170) into groups of 10... 17 groups with 10 students in each. Does anyone know how to do this please? I would also like to (next year) check that students don't end up in the same groups with simular people so to speak. Please help?

 

[namliam]

Make a query having a column: Rnd(Now())

This will generate a random number take the "top 10" rince and repeat...

Making sure nobody ends up in the same group with anyone they did last year is going to be a NIGHTMARE... For two years its probably quite possible,
i.e.
17 groups 10 people... 10 of the #1s in the groups make the new 1st group
The 7 #1s that are left + 3 #2s make group #2
The next 10 #2s make group #3
etc...

for 3 years its going to be pretty hard...
for 4 years near impossible....

 

[Atomic Shrimp]

Agreed - making the groups completely different each time is mathematically quite a thorny problem, and will prove impossible after a very short number of iterations - consider it from the point of view of one person:

I'm person number 1 in a group with persons 2 to 10

Next year, I need to be in a group with 9 people not in the range 2 to 10, but each of those people needs to have been in a separate group last year (or else they are in the same group as someone they were with last year).

It would be better just to randomise it, and accept that this is likely to include a bit of variety and a bit of year-on-year consistency.

 

[The_Doc_Man]

To really get this right might require some VBA programming. Doing it by query only will be a really serious problem. Adding the "not similar to other members of new group" becomes a really protracted pile of tangled spaghetti because NOW you have to also define what "not similar" means - by defining "similar" for your operational rules.

You need to ask yourself exactly what you want along the "dissimilar" lines and also think about why it really needs to be random.

 

[leo002000]

Dear all,

Many thanks for all your advice and comments. I'll give it a go and let you know how i get on. I think your totally correct in the way you suggest randomizing the data and then kind of accepting it's outcome, thus accepting that enough variety has been created in the groups.

 

[leo002000]

Hi,

I just created a query with the "Expr1: Rnd(Now()" in the end column/field, this generates a random number, but also creates that same number for all the other records/rows? so every student has a random number of "0.301948010921478" what am i doing wrong?

 

[gemma-the-husky]

you need to force the rnd function to be called for all rows

try this - have a FUNCTION in a module

Function myrnd(id)
myrnd = Rnd(Now)
End Function

and make your query column

myrnd(id)

where id is some identifier in your query. this will force the function to be evaluated each time - ie for each row
---------------
however - you may need to find a different way to do this, or to store the generated number, as a rnd using now as a seed will be different each time - and you wont be able to repeat the results to test the rest of your prog.

 

[ajetrumpet]

Leo,

If you are astute with programming, this stuff will be very useful to you:

http://www.access-programmers.co.uk/forums/showthread.php?t=157748

You can simplly loop through the records in your table, randomize them with that code, then spit them out in order, randomized. Then group them in order...1-10, 11-20, etc...

I will gladly give you a sample if you'd like. This is fun stuff.

 

[namliam]

"Expr1: Rnd(Now()"

Hmz, yes thats my bad... Do what GTH suggested...

In 17 groups of 10, i think its quite easy to make it work for 3 years... however as more yours pass, its going to be harder and harder to find groups that will fit this demands...

So the question becomes... How many years does this need to work?

 

[Lightwave]

Combinative mathematics - yes pretty difficult - I am aware of a league table setup that uses it to calculate who plays who.

I got code from this lad called Dev Avish who I think is a Access MVP. I put together a small database and posted an example of it on here

http://www.access-programmers.co.uk/forums/showthread.php?t=105677

I just had a bash at altering it to see if I could get 170 teams into it..

The idea I had was that you could substitute the teams for names of students and it would produce a league where everyone played everyone else over 169 days. So everyone would appear to have 169 matches. By taking blocks of 5 matches starting from the top of the fixture list you would group 170 people into groups of 10.

Now this is where it gets sketchy. I thought that week 2 or day 2 games would be a separate set of 85 games with people playing different people and you could make up a new block of 5 matches.

The idea that the substituted individual students would have been randomised as if they were teams such that every individual student/player played every other individual over the league period but there was never an overlap of people having to play more than one person on the same day or fixture period. So for you the first 85 games would have been a clean group of 17 groups of 10people in what would equate to the first period you are interested in and the second 85 games would be the next set of 17 groups for the next period you are interested in. Of course the coding also works out the random groups for the next 167 periods!

Given that everyone in a 170 team league would play everyone else such that they would have to have 169 games it would take 169 days/student periods at one game a day or 169 weeks for everyone to have been in a pairing with everyone else.

Of course this randomises students for one vs another student and I can't figure out whether this would also randomise students for block group of 5 games or groups of 10 students. Looking down it though I suspect to all intensive purposes it may well do. Certainly the iterative procedure getting through all the different combinations looks like it does the job.

Well went through it and for a start several things started happening.

In my original explanation I thought that there was no upper limit on the number of teams it could put through a league. Sorry but that turns out to be wrong. There was one limit of 50 placed on a parameter and also other variables had to be changed to type long rather than integer.

I got it as far as calculating a league for 98 teams...

However when I pushed to 100 it fell over say

with the following error

run time error '9'
subscript out of range

and then the debugger took me to the following line
rstFixtures.Fields("Player2") = TeamNames(Player2)

Don't have time to figure it out at the moment..

But if anyone knows an easy way to solve this please tell me.

Mark

 

[namliam]

I tried doing a little something something...
Having 1-10 in group 1 first
then 11-20 group 2 etc...

Then 1-11-21-... group 1
101-111-121-... group 2

But already on the third run I run out of options
Having 1-12-23-32...etc in group 1
95-101-112... group 2
13-21-34 group 3...

When I come down to group 16 ... I am out 3...

This method is based upon...
Take #1, skip all untill you get to 11... Then 1-20 already are not possible so take 21 ...etc... have a look...

To do it (note it is not supper fast!) go into module 1 and run "SecondYear".

I also have a random one... "DoItRandom"

Both are mostly query driven