Using one form to populate into multiple tables.

[bitesize910]

Hi everybody, I am very new to Access, and am looking to use access as a way to create a database for the volunteers we have at work on a weekly basis. I have created a couple of tables, and reports that I would like to have filled in using one form. This way, when myself or my boss are away, other staff members can enter in volunteer training schedules easily without going through multiple files/documents on the computers. Essentially, I would like to create one Form for staff to have access to. I would like this form to populate a Master Log (a table where we keep all of our schedules created for the volunteers) and Individual Training Reports (where I can see where one particular volunteer has been trained, as what date and time, and by whom on a report). For example, if Wanda came in today and trained at 3 different stations for one hour each, I could select Wanda on the form (I know how to do this at least! ) enter her schedule in the form, and the form would populate the Master Log, as well as her particular table/report (not quite sure how reports work just yet). If I were to select Jamie and enter a schedule for her, it would enter it into the Master Log and Jamie's particular training table/form.

The reason for the Master Log is so that we can easily search for and find an individual volunteer and where they might be stationed at during and after training. We use this information to track where each volunteer has been stationed at, and on what days. The reason for the Individual training table/report is so that we can see exactly where each volunteer is in their training, and what employee trained them. They only come in once a week (some bi-weekly) so they are at very different levels in their training and we need a way to ensure that we keep track of it since we work around animals as well as people. To keep it simple, we'll keep Jamie and Wanda as the examples for what I have so far in Access.

I have one training table created for Jamie and one table created for Wanda. For both tables, the Field Names are in bold, and how it would look on the table:
Exhibit_1 Training_Date_1 Employee_1 Training_Date_2 Employee_2 Passed_Training


Stingrays 12/24/19 Ashley 12/31/19 Ashley Passed
Otters 12/24/19 Ashley 12/31/19 Ashley Passed
Flamingos 12/31/19 Ashley 1/8/20 Ashley Needs More Training
Dolphins 1/8/20 Ashley

For the Master Log, the field names are in bold and this is how it would look:
Volunteer_ Date_ Exhibit_1 Exhibit_2 Exhibit_3
Wanda 1/8/20 Stingrays Otters Flamingos
Jamie 1/10/20 Dolphins Stingrays Otters

For now I'll make my questions a little easier to start. How to I select Wanda and an exhibit name on a form, hit a submit button (I know how to make that button, too! Not all is lost!) have it populate the volunteer and exhibit_1 slots on the Master Log AND populate Wanda's training log (But not Jamie's) with the exhibit name?
Let's say, for now, the Form only has an area to select the Volunteer and Exhibit_1.

If I know how to do one of the slots, I'm sure I can figure out the rest pretty easily. I hope this made some sense.

Thank you!

[June7]

Should not have separate tables for each employee. Use 1 table and apply filter criteria.

Multiple similar name fields indicates table structure is not normalized. What if someday you have Exhibit 4? The design you show is path to insanity.

Use form/subform arrangement for data entry.

Basic Access functionality. Any introductory tutorial book should have guidance on data normalization and form design.

[bitesize910]

Oh wow, I didn't even notice the spacing in the tables area didn't show up like intended. That definitely leaves for lots of confusion. I didn't even know to look for subforms. For clarity, Jamie and Wanda are the volunteers in training. Ashley is a random employee doing the training (which is much less important in our logs. Just good info to have off-hand). These are the Field Names used for the Master Log table:
Volunteer_
Date_
Exhibit_1
Exhibit_2
Exhibit_3

Field Names for Training tables/reports:
Exhibit_
Training_Date_1
Employee_1
Training_Date_2
Employee_2


Thank you for pointing me in the direction of subforms. I'll be checking that out now.

[June7]

That restating of info doesn't really make any difference. My comments about data normalization still apply.

[bitesize910]

ok, no problem. Just wanted to make sure I was clear in what I needed to say. I definitely have some learning to do. I had to look up normalization . I appreciate your honesty, and will be adding more exhibits within the next 3-4 years (though our number now is much larger than 4 ) so that is something I hadn't thought about prior to creating these tables. No one I know or work with knows how to use access, so I'm trying to learn this on my own via the internet. Thanks for the help!