Mult. courses for an instructor

[Paul A Mountcastle Jr]

All:

I am trying to generate a list of instructors with the course(s) they teach for the fall and spring semesters. I want to use the output for an MS Word mailmerge. I would like to see: Instructor info (name, address), course 1, course 2 (if there is one), etc.

To complicate matters, I am focusing on part time faculty only.

Below is the SQL view of the query I have built thus far, using three subqueries. The result give multiple rows for instructors who teach more than one course/semester.

Having problems wrapping my brain around this one,

Paul-

SELECT [Part Time Faculty].[Last Name], [Part Time Faculty].[First Name], [Part Time Faculty].[Street1 Line1], [Part Time Faculty].[Street1 Line2], [Part Time Faculty].[Street1 Line3], [Part Time Faculty].City1, [Part Time Faculty].State1, [Part Time Faculty].Zip1, [Part Time Faculty].[Natn Desc1], [Fall 2004 Courses].[Subj Code], [Fall 2004 Courses].[Crse Number], [Fall 2004 Courses].Title, [Fall 2004 Courses].[Credit Hours], [Fall 2004 Courses].[Day for Letter], [Fall 2004 Courses].[Begin Time1], [Fall 2004 Courses].[End Time1], [Spring 2005 Courses].[Subj Code], [Spring 2005 Courses].[Crse Number], [Spring 2005 Courses].Title, [Spring 2005 Courses].[Credit Hours], [Spring 2005 Courses].[Day for Letter], [Spring 2005 Courses].[Begin Time1], [Spring 2005 Courses].[End Time1], [Part Time Faculty].Salary
FROM ([Part Time Faculty] LEFT JOIN [Fall 2004 Courses] ON [Part Time Faculty].Id = [Fall 2004 Courses].[Primary Instructor Id]) LEFT JOIN [Spring 2005 Courses] ON [Part Time Faculty].Id = [Spring 2005 Courses].[Primary Instructor Id]
ORDER BY [Part Time Faculty].[Last Name];

 

[neileg]

Are you sure you need this in Word? It is very easy to handle this data in an Access report, grouping by the instructor. Merging into Word is much more difficult. Try searching in the Word forum on here, as I think it's been covered before, but the report options wins every time.

 

[Paul A Mountcastle Jr]

unfortunately, i do need something in Word, as I am printing on letterhead. I will search the Word forums here, though. thanks for the tip.

Paul-

 

[KKilfoil]

unfortunately, i do need something in Word, as I am printing on letterhead. I will search the Word forums here, though. thanks for the tip.

Paul-

If you are not adverse to a bit of code, write a function that selects all of the course records for a given instructor, and then concatenates them into a single string, which is then assigned to the function return value.

Then you can easily write a query that shows each professor and a calculated text field (using the above function). Then the number of 'things' per record to merge to word is constant, making things much less of a headache.



BTW, your database may have some normalization problems. Have you considered having a SINGLE course table with one more added field for Session, rather than separate tables for every session?

Then, your queries can specify the session as needed, and you won't have to do it all again in 4 months or a year when a new session is to be defined.
HTH

 

[Paul A Mountcastle Jr]

thanks for the info. i do have the table somewhat normailized, with each course uniquely identified by semester code and course id. withing each course record is the instructor's id. i think i need to create an intersection table to avoid a potential many-to-many table setup.

thanks again,

Paul-

 

[neileg]

unfortunately, i do need something in Word, as I am printing on letterhead. I will search the Word forums here, though. thanks for the tip.

Paul-

Ok, but you can still use letter head with reports, or embed your logos and details in the report and print on plain paper.