DB Design is a big Brain Twister

[airbus2]

I have a training db desinged to create classes and maintain employee records of classes attended. With the current db design,when a new class is created, a class title is required to be typed in the "class name" field and then any assignments that are required (ie. Quiz, Test, etc.) have to be individually typed into a subform as well. The only problem is that each class is 2 days long and we offer 5 different classes a week; can have up to 15 different assignments depending on the class.Thats a lot of time typing into the db. So in my attempt to automate this process, I created a table with all the "class titles", and another table with all the possible "assignments" associated with each class title. What I am wanting to do (via subform) is that when a "class" is created, they will first select a "Class Title". Depending on what title they select the "assignments" associated with that title will also be displayed. Can I have a unbound combo box with a drop down list all the "classe titles", and that trigger the associated "assignments" to show in the "assignments subform"? I currently have the "class title" and "assignments" in a join table. (ClassTitleAssignmentJoin)(primary key-"Class_Title_Assignment_Join_ID","Class_Title_ID", and "AssignmentID". And the same join table between the "ClassID" and "ClassTitleID". I hope this is understandable, I am a novice at Access 07

 

[MajP]

To help out we would need to better understand your tables and relationships. You can use Faq700-6905 or use a similar format to show us your table structure. The short answer Yes it can be done, but we need that information to be more specific.

I refer to your class and assignment tables as reference tables. You are not adding a lot of data to these tables, but using the information in them to reference other tables to them.

The question is do you need to actually provide the employee a default set of assignments that can then get edited, or just show that if they pick a class these are all the assignments.

The latter is easy, that just is a subform that links the employee, class, and assignments.

The former requires an insert query. So that when you pick a class for an employee the insert query puts the foriegn keys for each assignment (or other assignment information) into another table. Based on the fact that originally you said you had to enter a lot of default information I assume this is what you want to do.