Create Tables via VBA

[StryderLlama]

I have three tables. One called tblRound, one called tblLanes and one called tblCars. I would like to use VBA to create from those three tables a set of tables. tblRound would determin how many tables get created. tblLanes would determin the columns for each table and tblCars would determin the records in the table. Then I need to auto arrange the tblCars values in a random order in each table. I am attaching a screen shot of how the tables should look. What I am doing is auto-generating races for a Pinewood Derby. Any idea how I would go about doing such a thing?

 

[Mr. B]

You would not want or need to be creating tables on the fly. This is almost never a good idea.

What you are experiencing is what is know as a many-to-many relationship. This situation is created by the fact that you can have a combination of any of the values from multile tables.

Try creating a table that will let you combine all three tables by using the record ID for each value from the other tables. You might call this the "tblRace" table.

You new "tblRace" table would have a field to hold the record Id from each of the three tables. So you would then have a record in this table for each lane including the "round" and the "car". (You may actually find that you need to store more information in this table realted to each race.) You can then query this table to determine which car is in which lane in which race.

Hope this helps.

 

[Rx_]

Agreed, creating tables for this would not be a good solution.
http://hoopstournament.net/Database.html
Here is a MS Access 2007 (updated since 97) for running basketball tournaments.
It is not exactly what you are looking for. But, it should give you some good ideas on how to design something very close.
Perhaps this example will inspire a good design for your applicaiton.