The workshop code is already broken down into segments, if a workshop is run at another Campus with the same title, it still gets a unique code.
The core part of the code (the part that is independent of the campus or the session etc.) should be in 1 table with the name of the course. Now if the same code letter is attributable to a campus location then that should be in a table that houses those locations. You would join the tables in a third table
tblWorkShops
-pkWorkShopID primary key, autonumber
-WCodeCore
-txtWSName
tblCampus
-pkCampusID primary key, autonumber
-txtCampusCode (that portion of your original W_Code attributable to the campus)
-txtCampusName
Now when you have an actual workshop you would bring the workshop and campus together
tblWorkShopCampus
-pkWSCampusID primary key, autonumber
-fkWorkShopID foreign key to tblWorkShop
-fkCampusID foreign key to tblCampus
-dteWS (date of the workshop at the particular campus)
Now since you will have many students attending a work shop at a particular campus that describes another one-to-many relationship. Further, since a student can attend many workshop at any campus, you have another one-to-many relationship resulting in a many-to-many-relationship
tblWorkShopCampusStudents
-pkWSCampusStudentID primary key, autonumber
-fkWSCampusID foreign key to tblWorkShopCampus
-fkStudentID foreign key to tblStudents
If the user wants to view all the workshops given or select all/some of the workshops given, they need all the codes in one spot. Right?
Even though the code information is in separate tables, you can easily join them back together if for your users to see. Your users should never see your tables; all interaction should be through forms.
I can see moving the Learning Outcomes to a seperate table and tying them into the Workshop table via [WCode] but when we create a workshop, we assign up to 3 LO's to that workshop. Because the workshop id etc is just being created there will not be a record yet for it in the LO table? Why not just leave the LO's where they are? What unforseen danger lies down the road with the current method?
As discussed previously, you have a many-to-many relationship between a workshop and the learning outcomes. The structure would look like this:
tblWorkshopLearningOutcomes
-pkWSLOID primary key, autonumber
-fkWorkshopID foreign key to tblWorkShops
-fkLOID foreign key to tblLearningOutcomes
How does the user select the LO's for that workshop they are creating/filling in if the LO's are stored in a different table.
You would need to create a main form based on the workshop table with a subform based on the tblWorkshopLearningOutcomes. In that subform, you would have a combo box based on the tblLearningOutcomes so that the user can select a learning outcome applicable to the workshop. If the learning outcome is not listed in the table, a new record will need to be created. There are ways to do this via the Not in List event of the combo box.
What unforseen danger lies down the road with the current method?
Plenty of dangers. Trying to query with the design you currently have would be a nightmare. Such as try to find all workshops that have a particular LO. You would have to search multiple fields each time rather than a group of related records. Further what would happen if you decide to capture more than 3 LO for a workshop? You would have to redesign your table and ALL associated forms, queries and reports---not something I would want to do!
