Help with Normalization - Courses/Labs

[alpinegroove]

Let me try to review the required forms:

1. frmRelatedLecturesLabs
Form linked to tblCourses, filtered for only lectures.
Subform linked to tblCourseLabs; Combo Box filtered to show only labs.

Question: How does this relationship come into play since CourseLabID is not linked to a FK?

2. frmRelatedCoursesSections
Form linked to tblCourseSections - should this be filtered?
Subform linked to tblRelatedCourseSections

Same question as above: How does this relationship come into play since RelatedCourseSectionID is not linked to a FK?

3. frmCourseSectionPeopleRoles
Form linked to tblCourseSections
Subform linked to tblCourseSectionPeopleRoles

Is that right?
Am I missing anything?

 

[jzwp22]

Question: How does this relationship come into play since CourseLabID is not linked to a FK?

It is not linked based on everything you have described, CourseLabID would just serve as the primary key of the table. The sole purpose of the table is to show which labs are related to which lecture (i.e. Math 101 v Math 101 Lab)

Same question as above: How does this relationship come into play since RelatedCourseSectionID is not linked to a FK?

Again, the purpose of the table is just to relate those sections that are related. RelatedCourseSectionID is just the primary key.

3. frmCourseSectionPeopleRoles
Form linked to tblCourseSections
Subform linked to tblCourseSectionPeopleRoles

You could take this further:
start with a form based on tblCouses with a subform based on tblCourseSections (so that you see all applicable sections for each course) and within the subform have another subform (i.e. subsubform) that is based on tblCourseSectionPeopleRoles in order to see the people associated with each particular section.

 

[alpinegroove]

I will try this and see what happens. Thanks again.

 

[jzwp22]

You're welcome.

 

[alpinegroove]

I am still working on the forms. I have only created:

1. frmRelatedLecturesLabs
Form linked to tblCourses, filtered for only lectures.
Subform linked to tblCourseLabs; Combo Box filtered to show only labs.

I linked between a few lectures and labs.

For testing purpose, I wanted to run a query that shows me which lecture is linked to which lab.
I added tblCourses and tblCourseLabs to the design view of the query. CourseID is automatically linked with the two FK in tblCourseLabs.
but this query does not show me the results I thought it would.
How would I get a list of the which lectures are associated to which labs?
Thanks

 

[jzwp22]

The self joins require a little editing. First, remove one of the two join lines between tblCourses and tblCourseLabs. Then add tblCourseLabs a second time (Access gives it the alias tblCourseLabs1). Establish a join from this table to the field from which you removed the join. You'll also have to give the course title field an alias. This is what the SQL text would look like

SELECT tblCourses.CourseID, tblCourses.CourseTitle, tblCourses.Units, tblCourses_1.CourseTitle AS RelatedLab
FROM tblCourses AS tblCourses_1 INNER JOIN (tblCourses INNER JOIN tblCourseLabs ON tblCourses.CourseID = tblCourseLabs.fkMCourseID) ON tblCourses_1.CourseID = tblCourseLabs.fkLCourseID;

 

[alpinegroove]

That worked, thanks. In the design view, I now see tblCourses, tblCourses_1, and tblCourseLabs.
tblCourses_1 is lined to tblCourseLabs with a one-to-many linked (like in the Relationships window) whereas tblCourses is linked to tblCourseLabs with a simple black line.
Is that something to worry about?

 

[jzwp22]

Is that something to worry about?

No, since you have referential integrity enforced in the relationship diagram you should be alright. You will not be able to add a related course via the query (only the main course), so you should stick to your forms for that.

 

[alpinegroove]

Thanks for the explanation. I am making slow but steady progress here. Thanks for hanging in there...

 

[alpinegroove]

Ok, I think I made a lot of progress. I am attaching a copy of the database with all the aforementioned forms. Everything seems to be working, but there are some new questions:

1. Is it possible to create a form with subforms and/or subsubforms that lists a Course Section at the top, then only those labs/discussions related to it? I need to be able to assign instructional staff to both the lecture and labs, and it would be convenient to this on the same form.

For example: at the top of the form I would see Math 101, Section 1, MWF 10-11, 100 Skyler Hall. Below I would see a form that allows me to assign an Instructor and a Reader to the lecture, and below a form that allows me to assign a TA to the labs related to that lecture.

2. What kind of query would work for my contract needs mentioned above? I basically need a query that lists one entry per person per lecture section.

For example, if Smith teaches both the Math 101 lecture and all 3 labs, I would need them to be listed in the same row, along with his contact information, the days and times of his classes, and his compensation.

If Smith teaches another lecture and/or labs for another lecture, he would be listed in a separate row and would receive a separate contract.

If Smith teaches the lecture, but someone else teaches the labs, they would be listed in a separate row and receive separate contracts.

 

[alpinegroove]

I got at least part of question 1 from above. I based the form on tblCourseSections, so I am actually seeing the Section, not the Course. That's the easy part. I then created a subfrom linked to tblCourseSectionPeopleRoles, where I am able to assign people to specific roles for that Section.

However, I think I now need another subform or a subsubform to assign people to roles for Labs/Discussions, but only those related to the Lecture section.

It probably needs to be linked to tblRelatedCourseSections and perhaps some kind of query, but I am not sure how to accomplish this.

Any suggestions?

Thanks