2 faculty on one report

[snu]

A course can have co-teachers so I have 2 teachers teaching the same course at the same time. I solved the problem of creating a 2nd faculty in my form, but now need help w/my report.

I have tblFaculty, jtblFaculty_CourseSched, tblCourseSched

I have the following fields in tblCourseSched: FacultyID1, FactultyID2, CoursesID, DaysID,Time, LocationsID, RoomID,SemesterID.

I am trying to add FacultyID2 to my report.

I have a control labeled FacName1 for FacultyID1 and I tried to create a duplicate control for FacName2. I went about this by adding tblFaculty to the SQL statement and then adding tblFaculty1 (a duplicate of tblFaculty) for the 2nd faculty. In my expressions I referenced the table for each. This is giving me an error message. How do I get the 2nd Faculty control to work? Which way should the joins go between tblFaculty1 and tblCourseSched? I tried making the same as tblFaculty and tblCourseSched but this doesn't work.

Here is the expression I tried using that doesn't work.

FacName2: IIf(Len([FacultyFirstName]![tblFaculty_1] & "")=0,[FacultyLastName]![tblFaculty_1],[FacultyLastName]![tblFaculty_1] & ", " & [FacultyFirstName]![tblFaculty_1])


How should I/can I add it to the form?

 

[rainman89]

Can you post up a sample of your database so we can see what is goin on.

 

[snu]

Here is the database. I have successfully (I think) added a 2nd instructor to frmCourseSched which allows me to assign two instructors to the same course for the same section and semester.

Where I need help is getting 2 instructors on my rptCourseList (and other reports, but once I see how to do it I can do the others). When you open it you will get an error bc whatever I was doing isn't working. If you open another report you can see how I've been listing the course/instructor in the same field. I don't mind listing both instructors in the same control as they will both be associated w/ a particular class that has its own PK.

Any help is appreciated! Sorry it took me so long to post, got busy w/other stuff!

Thanks!

 

[rainman89]

You have your field designations wrong in your query... You need to do it tablename!fieldname.

You are doing it backwards


it should be

FacName1: IIf(Len([tblFaculty]![FacultyFirstName] & "")=0,[tblFaculty]![FacultyLastName],[tblFaculty]![FacultyLastName] & ", " & [tblFaculty]![FacultyFirstName])

And

FacName2: IIf(Len([tblFaculty_1]![FacultyFirstName] & "")=0,[tblFaculty_1]![FacultyLastName],[tblFaculty_1]![FacultyLastName] & ", " & [tblFaculty_1]![FacultyFirstName])

 

[snu]

D'oh!

Such a simple error! But I can't believe I had the right idea w/adding a second faculty table. I was really guessing my way through it.

Was that the right way to do this?

Good eye for detail!

Thanks!

 

[rainman89]

D'oh!

Such a simple error! But I can't believe I had the right idea w/adding a second faculty table. I was really guessing my way through it.

Was that the right way to do this?

Good eye for detail!

Thanks!

Yeah, it will work as you want it too!