Populate data from one form into multiple tables

[JoeBruce]

Greetings -

First time posting, first time designing a database. I have a pretty good start so far, but I've hit a snag and just can't sit through more internet video tutorials today.

I run an environmental education program, and the goal of the database is to not only store teacher information, but cross-reference that with programs they have attended and when. I have created two Tables: one for teacher information (basic data of names, emails, phone numbers, and school info) and one for programs attended (type of program, date of program, but also the teacher & school name). I have the primary key from "tblTeacherInfo" linked to a foreign key in "tblProgramsAttended" with a one-to-many relationship.

I created a form to enter/view teacher contacts, and it has a subform so I can also enter/view the programs attended and date (the subform only has those two Records).

Here's my issue: when I enter the teacher info, I want the name and school to name to also populate into "tblProgramsAttended." I am committing a database design sin by wanting duplicate info on two Tables? As I type this I'm starting to think I should be re-thinking my design...

I would like to be able to use this data to look at how many of a particular program I have done, how many programs a specific school and/or teacher has done, how many programs in a specific date range, etc. But I should be able to do that with reports and queries, and would not need the teacher/school name in my Programs Attended table, correct?

I may have just made my own question obsolete, but I will still post this in case anyone has input into that original question (how to populate data from one Form into multiple Tables) or general input into the design of my database.



Regards,
Joe Bruce

[ssanfu]

It would not be something you would want to do.
Storing data redundantly breaks the "Rules of Normalization". And the normalization police are very strict! Be warned.


If you want the teacher name with the programs for a report, use a query.
Add both tables to the query, link on the PK/FK fields, drag down the fields you want to see, and Bob's your man!

Use the query for the records source for the report.