Where to Put Instructor Salary?

[Rainlover]

It is not standard. 
There are certain loose guidelines, but it does vary from person to person and/or course to course.

This may or may not fit in with your design but here goes.



Create a Table. Call it tblSalary.

PK "AutoNumber"
Description "Text"
Wage "Currency"

Enter some data in Table DataSheet view. This Table does not need to be Related to any other Table/s

In your form where you enter the Salary, Bind a Combo Box to a Field where you are going to store the Salary. "Currency" This is not a Foregin Key as you will be storing the actual Salary in $0,000.00 Format.

Link the Combo Box to the new Table tblSarary so that you can pick from the list. In the Properties of the Combo Box DO NOT LIMIT TO LIST.

This way you can either select a preexisting Value or Free Type a new one. The Result will be stored in the Record Source (Table) behind the Form.

This is not normal practice but may be a good option for you as not All Salaries are Standard.

[alpinegroove]

Should the actual salary for each instructor, course, role combination still be stored in tblCoursePeopleRoles?
If so, what am I gaining by creating tblSalary?

[Rainlover]

I did not realise that tblCoursePeopleRoles was already there to store salaries.

So use it and forget tblSalary.

[alpinegroove]

Here is a copy of the database.
frmCourseInfo is used to create the association between instructor, course, and role.

tblCoursePeopleRoles contains the salary information for each instructor, course, and role combination.

qryCompensation is the cross tab query that yields a table that lists the salary of each person for each role he or she plays in a given course. If the person is associated with more than one course, he or she is listed multiple times. This is necessary since I need to issue a separate contract for each course the person is associated with. The contract then needs to list the compensation for each role the person holds in the course.

Reminder: the main question is where to put the salary information in way that will allow me to create the contracts. Since it has been suggested that a cross tab query should be the last resort, is there another way to achieve this?

Thank you!

[Rainlover]

Could you please look at the new query I created and then tell me what is right and what is wrong.

PS

I like your variation on the naming of your primary keys.

The following could be a slight improvement.

KeyNamePK
KeyNameFK

It is what I use and I find it less confusing.

[alpinegroove]

Thank you. Let's look at Math 3A, for example.
Smith is both the Instructor and the Reader.
Because he has two roles in the same course, he is listed twice in the query results.
However, he needs to receive ONE contract, listing the compensation amounts for each role.

Since I am looking for an automation solution, either with Mail Merge or VBA code, I don't know how I can accomplish that given that one contract will need to incorporate two different records.
It might get a bit more complicated because some people are associated with more than one course and need to receive a separate contract for each course.

[Rainlover]

I would be looking at a Report that has a Sub Report.

[alpinegroove]

How would I use a report to create contracts from a template?

[Rainlover]

A Report is a Template. You can free type whatever you wish to say, then add some Controls (eg Texr Boxes) that get their information from a Record Source (Query or Table)

You have already done something similar when you created the Form frmCourseInfo. You have Labels which can be quite large and contain a lot of Text.

You have a subForm where you have displayed information that is related to the main Form.

A report is similar to this. In the Main report you could display the Teacher information like, Name, Address Course etc and then in the sub form you can display the various Roles associated with the Course.

This is a bit teadious to design but then so is a Mail Merge Document. Once done you can use it for any Contract.

There would be a Bunch of examples on this Site of Reports with SubReports for you to look at. If all else fails then there is always Google.

[alpinegroove]

Thanks for all your help.