Where to Put Instructor Salary?

[Rainlover]

There isn't a uniform salary for any course, person, or role. The salary is adjusted manually for each combination.

If the Salry is entered manually then there is no need for a separate Table. Tables are used for storing repeating Data. As there is no repeating Data there is no need for a separate Table.

Again lets get the structure correct before designing the query. It would be wrong to design a table structre simply to suit one query.

[orange]

As Rainlover points out, the database and application have to meet your needs. You must get your structure correct before worrying too much about Access details. A key point in design is to identify your business needs.

Contracts may start and end on specific dates, and if an instructor/TA or Reader is terminated mid-stream, or dies unexpectedly, or leaves for another post, what exactly your organization does in these circumstances is what your application and database must support.

Rainlover and I (and others) can comment all we like, but it's your business and your processes that this database and application must support. So, identify what your business is; what are the things involved; how do they relate one to another; and what "business activities" create, use the various things. What circumstances are we likely to encounter and how would/do we deal with them. Then ask "Can this database support these things?" Maybe somethings are outside the scope - maybe not - who would know, who to ask?? Lot's of questions, but this is what makes up "your business" and the design of your database and application.

Good luck.

[alpinegroove]

So which table should I put it in if there is no need for a separate table?
I am not sure I understand what you mean by "tables are used for storing repeating data."

[alpinegroove]

If an instructor can no longer function for whatever reason, their roles for a specific course will be deleted and a different person will assume those roles. That is, the records associated with their PeopleID will be deleted from tblCoursePeopleRoles.
How does this affect where to put compensation information?

[RayMilhon]

first what makes the salary Unique? based on your example

John Smith | Math 1A | $5000 | $2500
John Smith | Math 32 | $3500 | $3000

It's dependent on 3 items: the Instructor, the Course and the Role. So If John Smith was the instructor for course Math 1A and both for Math 32 He would have the salary of $5000 + $3500 + 3000. so that would put it in the table tblCoursePeopleroles and result in 3 Records.


tblCoursePeopleRoles
-pkCoursePeopleRoleID primary key, autonumber
-fkCourseID foreign key to tblCourses
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRoles
-Salary

[alpinegroove]

Thank you, Ray.

John Smith will actually have 4 total roles.
For Math 1A, he receives $5000 for being the Instructor and $2500 for being the Teaching Assistant.
For Math 32, he receives $3500 for being the Instructor and $3000 for being the Teaching Assistant.

I need John Smith to receive 2 separate contracts.
Contract 1: Math 1A - Your compensation will be $5000 for Lecture and $2500 for Discussion.
Contract 2: Math 32 - Your compensation will be $3500 for Lecture and $3000 for Discussion.

If I put it in tblCoursePeopleRoles as you suggest, how would I use that data to populate the contracts in the manner described above?

[RayMilhon]

Create a Report in your database. Set up group headers on Person and Course Your detail would include the Role and Salary for that role. In the Course Footer you would have a sum of the Salaries. Make sure you set the forcenewpage property to after on the course Footer and set the repeatsection property to yes on the person header. Also be aware that if you use the forcenewpage property as specified you will get at least 1 extra page at the end of the report. This can be suppressed but I don't have the details in front of me. Perhaps one of the experts here can help with that.

[alpinegroove]

And then I use this report to create a mail merge somehow?

[RayMilhon]

The report would be the mail merge you can use the Query as a source for a Word Mail Merge document but you have more control over the formatting and grouping if you use an Access Report.

[alpinegroove]

I need to use my Word template for this. What kind of Access query can I create that would accomplish that?
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?

[RayMilhon]

You will have to create a crosstab Query. You will have to use the Person and course as the row headers. The Role as the column headers and the salary as the value.

[alpinegroove]

Wow, the crosstab query seems to work. I don't understand what it does exactly, but I think I can use this query for the mail merge.

[alpinegroove]

Ray, I have been playing around with the crosstab query.
I am add additional fields from different tables to query and designate them as row headers. They all end up on the same row, which is what I wanted.
How does the query know to do this? Without the relationships, will each row header be on a separate row?

[Rainlover]

Rather than use a Crosstab, you could use a Report that has a Sub Report.

[alpinegroove]

But I need to be able to use mail merge to populate my contracts in Word.
Can the report help me with that?