One Combobox saving to several tables?

[Kimberly3626]

I am in WAAAAAY over my head!!!
I am basically automating the training department in my unit. Here's what I
need to accomplish:
22 people, approximately 75 courses. Courses are devided into three phases
of training, and can fall in 'basic' for one person, 'intermediate' for
another, and 'advanced' for the third. Not everyone is required all
training, but if the course is listed, at least one of my 22 people need it.

I created a few tables. First is Course Titles. Not sure if this one is
even necessary!!! Then I created Basic Training, Intermediate Training,
Advanced Training and Courses Completed. All of these tables are IDENTICAL.
(seriously...I copied, pasted, and renamed) These tables have a column for
'training course Title' and then a column header for each of the 22
positions. ALL contents of the table aside from Course Title are yes/no
selections. (Is it required? Is it complete?)

I managed to make an organized form based on Course Title. Once a title is
selected, the form populates the data from the 4 tables (basic, intermediate,
advanced, and complete). It fills in all of my little checks for me. I can
then update or move the phases of required training based on updates and
pushes from higher ups.

Here's my problem: If the course title is already loaded into all 4 tables,
I can update/save the data. What I can't do: Add a new course title, check
my little check boxes, and have it populate all 4 tables with the new title.
I get indexing errors out the wazoo that 'this course title does not exist in
the index' bla bla bla.

So I tried going into the 'control source' for my Title field, then clicking
the ... button, and manually adding all of my Course Title fields from all 4
tables. No bueno. I think I really made it angry!!!

So...how do I make it work, or fix what I have already done to MAKE it work?
I debated on autonumbering IDs instead of titles so I could make it work,
but something happened in my original Course Titles DB to make it start with
number 2, so they would already be horribly off....

Eventually, I need to pull a report based on an individual's position that
tells me what training they are required, what phase it's in, and if it's
complete... I'll deal with the reports another day...for now, I'm at a loss
on inputting all of this stuff!!!

 

[Kimberly3626]

Ok. So now I just feel silly. After two days of beating my head against a
wall, I deleted all of the records out of my tables. Then I changed Course
Title to Course Title ID and linked em all. I recreated the form, including
all of the course ID column headers...then I made them invisible and voila!
It allows me to save it, autonumbers the basic, intermediate, and advanced
tables, and does what I need it to do.
Now to create the reports.... *sigh*

 

[Bob Quintal]

=?Utf-8?B?S2ltYmVybHkzNjI2?=

I am in WAAAAAY over my head!!!
I am basically automating the training department in my unit.
Here's what I need to accomplish:
22 people, approximately 75 courses. Courses are devided into
three phases of training, and can fall in 'basic' for one person,
'intermediate' for another, and 'advanced' for the third. Not
everyone is required all training, but if the course is listed, at
least one of my 22 people need it.

I created a few tables. First is Course Titles. Not sure if this
one is even necessary!!! Then I created Basic Training,
Intermediate Training, Advanced Training and Courses Completed.
All of these tables are IDENTICAL. (seriously...I copied, pasted,
and renamed) These tables have a column for 'training course
Title' and then a column header for each of the 22 positions. ALL
contents of the table aside from Course Title are yes/no
selections. (Is it required? Is it complete?)

You need to stop right here, and redo your tables.

You need the Course Titles.table. Add a field call courseID and
possibly other fields, such as CourseInstructor, CourseFrequency,
PassingGrade, etc; all things related to the course itself.

You also need a table for People. Name, Rank, Serial_number,
DateJoinedUnit, DateLeftUnit, that sort of thing.

Another little table is needed for training Level
Fields TrainingLevelID and TrainingLevelTitle.

Now comes the big change, Destroy your THREE IDENTICAL tables, cuz
that'sa a big no-no in database design.

You want 1 table, with the following columns:
CourseId
People.SerialNumber
TrainingLevelID (just the Number,1,2 or 3)
DateTaken
Pass (or grade)(your choice)

Once you have that, you can set up queries to return the data in the
way you want, and other ways too, which you will never be able to
make reports with your existing structure.

I debated on autonumbering IDs instead of titles so I could make
it work,
but something happened in my original Course Titles DB to make it
start with number 2, so they would already be horribly off....

Why, an ID number does not need to start with 1, nor does it need to
be continuous. Even if it's totally random, it still works.