Display if checked

[snu]

I'm am so confused!

I had to adjust my db to accommodate situations where 2 instructors teach the same course at the same time.

I have course names as one table.

I have another table that takes the course names, day, time, room, and teacher and makes this a unique coursesched.

I have had to adjust to allow for a 2nd instructor for courseched.

The problem I am having is that I create an observation report based on my observations of 1 teacher. For the class that has 2 teachers I will be doing 2 separate observations on 2 separate occassions, generating 1 report for each teacher (2 total).

Currently my report rptObservationResults shows the name of the faculty(s) that is associated with the unique coursesched. I have adjusted my other tables, forms, reports to show both faculty if there are 2 teaching the course. However on this report I do not want it to show both faculty as they are each getting their own separate report.

I am thinking there must be a way to check a box on my form frmObservationResults that feeds the report to indicate whether the report applies to FacultyID1 or FacultyID2. Basically on my form I want to check the box if the results are for FacultyID2 and uncheck if it's FacultyID1, then I would like that to somehow make my report show either FacultyID1 or FacultyID2 but not both.

Does this sound like a reasonable way to go about this? And if so how do I do it? If it works I would like to make some of my other reports refer to one or the other instructor as well.

 

[lcook1974]

I can't open 2007 DB's but my first thought would be to add a parameter to the query generating the report for you to choose which faculty member you want to see.

 

[snu]

Sounds good to me, but I have no idea how to do that.

 

[lcook1974]

In the query designer, under that field type

"[Enter Facutly Name] " or whatever the criteria is

 

[boblarson]

In the query designer, under that field type

"[Enter Facutly Name] " or whatever the criteria is

And for me, personally, I prefer using a FORM to select who you want (you can use a combo box) so that the user just selects an existing value instead of having to type it in and/or knowing what they have to type in (do they need to type in the full name, is the instructor listed in there as Ron or Ronald, Don or Donald, Bob or Robert?).

 

[snu]

Right now the form that feeds my report has a combobox that shows this in a dropdown list: "FacultyLastName, FacultyFirstName - 2ndFacultyLastName, 2nd FacultyFirstName CourseName-Section" (all in the same combobox). The user selects the correct course/instructors from the list. When a course comes up w/two instructor names, they would be selecting that course, then I need to somehow give an option to pick one or the other instructor.

So would I enter this criteria in this field even though it already shows both instructors?

 

[boblarson]

Right now the form that feeds my report has a combobox that shows this in a dropdown list: "FacultyLastName, FacultyFirstName - 2ndFacultyLastName, 2nd FacultyFirstName CourseName-Section" (all in the same combobox). The user selects the correct course/instructors from the list. When a course comes up w/two instructor names, they would be selecting that course, then I need to somehow give an option to pick one or the other instructor.

So would I enter this criteria in this field even though it already shows both instructors?

Sounds like you may have a normalization problem and need to fix your data structure first.

 

[snu]

Uh-oh.

Does this have to do with the way I created a second instructor or the fact that I am relying on CourseSched (the table that makes a class unique based on the time, day, instructor, etc.) for too many fields?

Most of my tables contain only one or two fields, but I just made CourseSched include 2 teachers (both referenced by the facultytable). Do you think this is the problem?

I have my db posted on this thread.

 

[boblarson]

If you have more than one field (instructor1, instructor2) that has instructor in it then you didn't do it right. It should be rows in a junction table.

 

[snu]

Sorry to be so pesky. But I don't understand what you mean by rows in my junction table.

 

[snu]

Thankfully I did a backup before I started messing with the 2 instructors. So if there is a better way to tie two instructors to one course I'd like to fix it.

 

[boblarson]

You would need to pull the two fields (Faculty_ID1 and Faculty_ID2) out of your course schedule table (tblCourseSched) and create a new junction table named something like:

tblCourseInstructors
CourseInstructorsID - Autonumber (PK)
CourseSchedID - Long Integer (FK)
FacultyID - Long Integer (FK)


And that way you can tie as many instructors as necessary to the schedule.

 

[snu]

I have a table called tblFaculty already. Would I still create this second instructors table?

 

[snu]

Nevermind... I see you included FacultyID so you already knew that. I'm going to give it a try.

 

[snu]

In looking into this, I already had a junction table, jtblFaculty_CourseSched that contains CourseSched_ID and Faculty_ID.

The two instructors have been working OK in everything else I constructed, I just list both instructors related to a course and in the case of the eval report I want to create a sep report for each instructor.

So does that mean my set up is correct? Is there a way to make just one of the two instructors show up in my forms/reports? My DB is attached in one of these posts.

Thanks!

 

[boblarson]

As for the junction table it has been a little while since I've seen your database and right now I can't dig into it due to some work I'm doing. But instead of using the two fields like you had set up to have the instructors assigned, you would do it using a subform in the form and then in the report you should be able to set it up with grouping to group on the instructors and then when you open the report you can use a WHERE clause to only grab that information that applies. I will try to see if I can't do something up later, if you can't get it. But I'm on a hot project today and so I can't spend a lot of time on anything else and I post here to take a short mental break when needed.