Where to Put Instructor Salary?

[alpinegroove]

I am building a database with information about courses and instructional staff in my school. Each course is comprised of a lecture (taught by Instructor); some courses have discussion sections (taught by a Teaching Assistant) in addition. The same person in some cases teaches both the lecture and discussion sections (and is therefore deemed both Instructor and Teaching Assistant for the course). In other case, there are different people teaching the Lecture and the Discussion; that is, one person is the Instructor and the other person is the Teaching Assistant. People can also serve as Readers.

To address the many-to-many relationship and assign roles, I use a junction table. This is the general structure:

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-txtAddress

tblRoles
-pkRoleID primary key, autonumber
-txtRole

tblCourses
-pkCourseID primary key, autonumber
-lngCourseTitle
-txtCourseName

tblCoursePeopleRoles
-pkCoursePeopleRoleID primary key, autonumber
-fkCourseID foreign key to tblCourses
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRolesWhere would be the best place to put salary information? Each course/role/person combination has its own salary. For example, for a given course, a person is paid x to be the instructor and y to be the reader for that same course. For another course, the same person is only paid to be the instructor. The salary amounts are additionally not uniform. They vary from course to course and are entered manually into the database.

The final goal is to populate contracts. Each instructor gets a separate contract for each course they are associated with. For example, John Smith is the Instructor for Math 1A and also teaches the Discussions in that course. He would get one contract that lists both his Instructor and Teaching Assistant salary for this course.
John Smith also teaches Math 15, where he is only the Instructor. For this course, he would receive a separate contract listing only Instructor salary.

So the main question is where in the db to store salary information given the model above?Should I create a tblSalary and link it to the junction table? That way, each course/instructor/role combination has its own salary.

But if I do that, how do I then prepare the data for the merge?

Thanks!

 

[spikepl]

The salary amounts are additionally not uniform. They vary from course to course and are entered manually into the database.

That implies that a given salary is specific to course, role, person. If that is the case, then there is no joy in a separate salary table, so the salary could be a field in your junction table. So if a person fulfils two roles in a given course, that would then warrant two entries in the junction table each with the specific remuneration.

What is "the merge" ?

 

[alpinegroove]

That implies that a given salary is specific to course, role, person. If that is the case, then there is no joy in a separate salary table, so the salary could be a field in your junction table. So if a person fulfils two roles in a given course, that would then warrant two entries in the junction table each with the specific remuneration.

That's right. That's how I understand it.

What is "the merge" ?

I need to use the information from this database to populate data in contracts (in MS Word). I will deal with how to automate that merge later, but for now I am trying to figure out how the data from the database can be used to create a mail merge to create the contracts.

As you write above, a person is likely to have multiple records in the junction table.
I need to issue a separate contract for each course. So an instructor who is associated with two courses will receive two contracts. Each contracts will list the salary for each role.
For example:
For John Smith
Contract 1 - Your salary will be $1500 for Lecture and $1000 for Discussion.
Contract 2 - Your salary will be $2000 for Lecture and $500 for being the Reader.

For Jane Johnson
Contract 1 - Your salary will be $3000 for teaching the Lecture.

Since in the junction table there are multiple records per person AND per course, how do I prepare this data to be able to merge the contracts described above?

I can see how to get all the salary components for a person, but how do I separate them so that if an instructor teaches two courses, one row shows the salary components for one course and the next row the salary components for the other course?

 

[spikepl]

So your problem is the variable number of merge fields for a given person and given contract. You could make a temp table with VBA, or fiddle something on the fly perhaps, with subqueries (http://allenbrowne.com/subquery-01.html), that would allow you to extract different bits from the same data and concatentate them (I haven't tried , but I would have a look at that)

 

[alpinegroove]

Thanks for the lead. I will take a look, though that seems somewhat beyond my skills.
Can anyone provide more information?

 

[alpinegroove]

It looks like what I was after is a crosstab query. I have designated course and name as row headers, role as column header, and compensation as value.