How do I make the creation of a record in a table trigger the creation of a new table

[learmanj]

I have a table that is a list of all of my events. Each record of events should have a child table that list all of the things that happened at the event. When a new record (event) is added how can I have a new child table created and linked to that record. Also I have a blank table to serve as a template for what each child should look like. How do I make sure this occurs? The child tables can have the same name as the index. I am just using numbers 1- for the index with 1 being the first event and so on?

[pbaldy]

Sounds like the design needs to be normalized:

Fundamentals of Relational Database Design -- r937.com

I would have a single child table with an "Event" field that related records within it to the appropriate event in the events table.

[ssanfu]

how can I have a new child table created and linked to that record.

You don't. Say you have 100 events (records). That would mean you would have 100 child tables..... totally unmanageable!! Not to mention the queries and reports...

You would have a table "tblEvents" with a primary key (PK). You have another table (what you call a child table) "tblSubEvents", that has the PK of the event table in a field known as a foreign key (FK)

The two tables are linked on PK - FK.
Each record in the main table (Events) is linked to 1 or more records in the child table.

tblEvents
------------
Events_ID (PK)
other fields


tblSubEvents
------------
SubEvents_ID (PK)
EventsID_FK (FK to tblEvents)
Other fields

[learmanj]

I was probably too general with my description of the problem think it would be easier to solve my problem. Hopefully if I get more specific then I can get a better answer. The problem maybe with my design.

The first table is a list of games in this case football games. So for a season the average team will have 9 games. Even if a coach has say 10 years of games he at most would have a maximum of 90 games.
The child table is a list of plays for each game. On average each game has a about 130 plays per game and their are about 40 fields of data for each play. As I read it, you suggesting that I put all of the plays for all of the games in one table. Is that Correct? If not what are you suggesting?

Sounds like the design needs to be normalized:

Fundamentals of Relational Database Design -- r937.com

I would have a single child table with an "Event" field that related records within it to the appropriate event in the events table.

[learmanj]

I was probably too general with my description of the problem think it would be easier to solve my problem. Hopefully if I get more specific then I can get a better answer. The problem maybe with my design.

The first table is a list of games in this case football games. So for a season the average team will have 9 games. Even if a coach has say 10 years of games he at most would have a maximum of 90 games.
The child table is a list of plays for each game. On average each game has a about 130 plays per game and their are about 40 fields of data for each play. As I read it, you suggesting that I put all of the plays for all of the games in one table. Is that Correct? If not what are you suggesting?

You don't. Say you have 100 events (records). That would mean you would have 100 child tables..... totally unmanageable!! Not to mention the queries and reports...

You would have a table "tblEvents" with a primary key (PK). You have another table (what you call a child table) "tblSubEvents", that has the PK of the event table in a field known as a foreign key (FK)

The two tables are linked on PK - FK.
Each record in the main table (Events) is linked to 1 or more records in the child table.

tblEvents
------------
Events_ID (PK)
other fields


tblSubEvents
------------
SubEvents_ID (PK)
EventsID_FK (FK to tblEvents)
Other fields

[pbaldy]

Yes, I'm suggesting a single table for plays, with a "game" field that contains the ID from your games table. At some point you're going to want to find out how many times a team has run a particular play this year. Hard to do if every game is in a different table.

[learmanj]

Okay trying to work out the logistics of this. It seems simple enough I just need to create 1 additional field in my game data table. But in my form that list the games, if someone in the record that is game #3. I want to create a button at the top so if they are in that record they click the button and it opens a form with only of the plays from game #3.
What would the logic be in the button?

If no plays have been entered yet for game #3 I would like a blank field with game filled in as 3.

I would also like it to auto populate the game # field every time a new record is added in that form. Is this do able?

Thank you for your help. I use to do all of this in excel and I think access will be much better. I took a college class this summer on access, but there are a lot things not covered an I am a newbie at this.
Jerome

Yes, I'm suggesting a single table for plays, with a "game" field that contains the ID from your games table. At some point you're going to want to find out how many times a team has run a particular play this year. Hard to do if every game is in a different table.

[learmanj]

I was able to do this:
I added a field the the game data.
I then added a button had it do a form Operation, Open Form, told it which form to open.
Had it open the form and find specific data to display. Game Number = Game Game Number
Put a Title on the button and named it something meaningful
Then hit finish

The only thing I was not able to do was auto-populate the Game Number field when I added a new record.

Thank you for all of the help

[pbaldy]

A subform is the typical way to handle a one-to-many relationship, and it will handle the linking field for you. If you want to use a separate form, you can either set the default value of the appropriate textbox to the field on the main form, or pass the value in OpenArgs and populate the records in code.