Fill table Random

[bodylojohn]

Hello,

I have a very complex issue.

First of all there is a table called "tblParTeam" wich contain the teamname and ID of teams partissepating in a certain Tournament. The tournemant data is kept in the table tblTournament.

Each tournament contains a couple of variables: "Number of teams per tournament", "Number of Rounds" wich reside in the tblTournament.

Here is the thing. I also have a form based on the tblTournament. Each tournament had a number of partisipating teams (stored in the variable "Number of teams per tournament"). Then I have a table called tblTournamentDetail wich containt the Tournament_ID, TeamPro_ID (looks in the table "tblParTeam"), TeamContra (also looks in "tblParTeam").

When I pusch a button on my form, wich is based on the tblTournament, tblParTeams and tblTournamentDetail, I would like the X (Number) of particepating teams to be filled in randomly in the tblTournamentDetail.

Lets say I have 10 teams I want 5 to be filled in randomly in the TeamPro_ID and 5 in the TeamContra_ID.
How do I go about this?

Thanks in advance.

 

[ajetrumpet]

John,

can you do this with a shuffle? I posted an array shuffler a while back. With it, you can bring in the data that you want from one table, throw the records into a 2-dimensional, and then toss them out one at a time to the table that you want.

Here it is...maybe you can get an idea from it...

 

[bodylojohn]

John,

can you do this with a shuffle? I posted an array shuffler a while back. With it, you can bring in the data that you want from one table, throw the records into a 2-dimensional, and then toss them out one at a time to the table that you want.

Here it is...maybe you can get an idea from it...

thanks AJ,

It's not exactly what I need.

I will describe the situation more clearly:

I have I table called tblTeams containing the participating teams of a tournament.

Then I have a table called tblTournamentRound containing the HomeTeam and the AwayTeam.

Now here is my problem:

When I push a button (example: cmdFill) I want all of the teams (lets say there are 10) to be randomly filled in in the HomeTeam and AwayTeams.
Sow there should be 5 rows in tblTournamentRound.

I hope that I discribed the problem clear enough.

 

[bodylojohn]

I hope that anybody can help me.

I tried to explain my problem as good as I could.

 

[bodylojohn]

I realy need some help on this please.

 

[Mike375]

The way I am reading this is you have 10 teams in the table tblTournamentRound and you want those teams to be randomly applied to tables HomeTeam and the AwayTeam

But am lost with...Sow there should be 5 rows in tblTournamentRound

Does that mean 5 of the teams in tblTournamentRound are assigned to HomeTeam and the AwayTeam so there will be 3 teams in one of those tables and 2 teams in the other.

Or does it mean that the 10 teams in tblTournamentRound have 5 assigned to Home Team and 5 assigned to Away Team and the assignment is done of a random basis.

Is you problem generating the random numbers or getting the Team details across to the other tables HomeTeam and the AwayTeam from tblTournamentRound

 

[bodylojohn]

Or does it mean that the 10 teams in tblTournamentRound have 5 assigned to Home Team and 5 assigned to Away Team and the assignment is done of a random basis.

Thats exactly what I mean.

Is you problem generating the random numbers or getting the Team details across to the other tables HomeTeam and the AwayTeam from tblTournamentRound

Escpecially how to generate random numbers from the 10 teams.
But also how to fill the 5 rows in the tblTournamentRound.

Thanks so far

 

[ajetrumpet]

John,

Just to get back to you on this subject...I'm sure you can do this stuff with an array of some sort. You're welcome to post the part of the file that you need randomized...

 

[Mike375]

Here is a straight forward way that is much quiker to do than to say it

Access can generate a random number. I know that Adam and others will say it does not fully qualify as a random number and that might be correct if you need to run Lotto.

Add a number to your table and in the Default value type in Rnd() Now make a copy of your table with just structure/definitons or simply make a copy and delete all records.

Make an Append query to append your table to the copy of the table. However, do not inlcude the field in the query that you created with the Rnd() default. Run the query and you will see now that the copy of the table has the field with Default/Rnd() full of random nunmbers. Now sort that field to A to Z and you will then see the field with teams are shuffled.

To make it an autumatic sequence do the following:

1) Make a Delete query to delete your table
2) Make a Delete query to delete the table that is a copy
3) Make an Append query to append the copy of your table to the original table but don't include the field you created with default Rnd() in the append.
4) Make an Append query to append the your original table to the copy of the table but don't include the field you created with default Rnd() in the append. This query is in fact the same query as you made above

You now need to run/open these queries in the right sequence with code or a macro. In the code or macro have SetWarning is No

Do them in this order

1) Delete the records in the copy of your table
2) Append the records from your original table to the copy of the table
3) Delete the records in the original table
4) Append the records from the copy og your table to the original.

Each time you run the macro or code that runs those queries the numbers in the Rnd() field of your original table will change. When you sort the Rnd() field you will jumble the team names.

Thus if the first 5 records are appended to HomeTeam and the bottom 5 records to AwayTeams the teams will be split on a random basis. If ran the macro that did the 4 queries and repeated the process then the teams in tables HomeTeam and AwayTeams would be different.

Getting the 5 records to HomeTeam and the 5 to AwayTeams is Part 2

That could be as easy as copy and paste depending on how the tables HomeTeam and AwayTeams are made. But see if what I have said is OK for you or if someone comes up with something better than the above.

If there are any legal type requirements for what you are doing I have no idea whether Rnd() qualifies. Perhaps Adam or someone else can comment. I do use exactly the above for name lists that are used for telemarketing and on 1000s of names the incidences of suburbs etc in every 100 records matches what would be expected.

 

[boblarson]

In order to use the RND() function you first have to use RANDOMIZE or else the RND will return the exact same set of numbers each time.

 

[Mike375]

In order to use the RND() function you first have to use RANDOMIZE or else the RND will return the exact same set of numbers each time.

Bob,

Not when the table appends backwards and forwards and the Rnd() is not in the append query.

 

[bodylojohn]

Here is a straight forward way that is much quiker to do than to say it

Access can generate a random number. I know that Adam and others will say it does not fully qualify as a random number and that might be correct if you need to run Lotto.

Add a number to your table and in the Default value type in Rnd() Now make a copy of your table with just structure/definitons or simply make a copy and delete all records.

Make an Append query to append your table to the copy of the table. However, do not inlcude the field in the query that you created with the Rnd() default. Run the query and you will see now that the copy of the table has the field with Default/Rnd() full of random nunmbers. Now sort that field to A to Z and you will then see the field with teams are shuffled.

To make it an autumatic sequence do the following:

1) Make a Delete query to delete your table
2) Make a Delete query to delete the table that is a copy
3) Make an Append query to append the copy of your table to the original table but don't include the field you created with default Rnd() in the append.
4) Make an Append query to append the your original table to the copy of the table but don't include the field you created with default Rnd() in the append. This query is in fact the same query as you made above

You now need to run/open these queries in the right sequence with code or a macro. In the code or macro have SetWarning is No

Do them in this order

1) Delete the records in the copy of your table
2) Append the records from your original table to the copy of the table
3) Delete the records in the original table
4) Append the records from the copy og your table to the original.

Each time you run the macro or code that runs those queries the numbers in the Rnd() field of your original table will change. When you sort the Rnd() field you will jumble the team names.

Thus if the first 5 records are appended to HomeTeam and the bottom 5 records to AwayTeams the teams will be split on a random basis. If ran the macro that did the 4 queries and repeated the process then the teams in tables HomeTeam and AwayTeams would be different.

Getting the 5 records to HomeTeam and the 5 to AwayTeams is Part 2

That could be as easy as copy and paste depending on how the tables HomeTeam and AwayTeams are made. But see if what I have said is OK for you or if someone comes up with something better than the above.

If there are any legal type requirements for what you are doing I have no idea whether Rnd() qualifies. Perhaps Adam or someone else can comment. I do use exactly the above for name lists that are used for telemarketing and on 1000s of names the incidences of suburbs etc in every 100 records matches what would be expected.

Dear Mike,

If I use your method my table tblTournamentRound gets randomly filled with some numbers.
But the ID from the team may be something else.

for example.. If i use rnd() the number could be 170. But there is no team with the ID 170.

i hope that you understand what I mean.

 

[Mike375]

The Rnd() field is only there to sort. It is a field you add to your table. It has nothing to do with your ID field.

Field1...... Field Rnd()

1.............343590
2.............--34348963495
3.............346903473479
4.............93429
5.............232348

Sort on the field that generates the random number.........and that will mix up Field1

The sequence of queries I gave you will generate new random numbers each time you run those queries...which you do with a macro or code.

It has nothing to do with your ID number or any other field.

If you are doing this correctly you will have added a field to your table.