Trying to Avoid Crosstab - Junction Table

[alpinegroove]

I have the following 4 tables:
tblCourses
tblPeople
tblRoles
tblCoursePeopleRoles

tblCoursePeopleRoles is a junction table that accomodates the many-to-many relationships between the records in the first three tables.

The junction table looks something like this:



Math 25, John Smith, Instructor
Math 25, Carol Jones, TA
English 17, David Black, Instructor
Spanish 1, Esperanza Martinez, Instructor
Spanish 1, Esperanza Martinez, TA
History 100, Jeff Michaels, Instructor
History 100, Jeff Michaels, TA
History 100, Susan Oats, Reader

Given my junction table, what kind of query can I run to identify, for example, the following groups:

People who are TAs and have no other roles (e.g., Carol Jones)
People who are Readers and have no other roles (e.g., Susan Oats)
People who are both an Instructor and a TA in the same course (e.g., Esperanza Martinez and Jeff Michaels)

The problem: if I add all my tables to a select query and set "TA" as a criteria, it returns everyone who has TA designation, including those people who are both TA and Instructor in the same course. I need to see only those who are TAs but have no other role in the same course.

How can I achieve that?
Thank you.

[June7]

How many Roles are possible - only 3?

Try:
SELECT Course, Person, Max(IIf([Role]="Instructor",[Role],Null)) AS Instructors, Max(IIf([Role]="TA",[Role],Null)) AS TAs, Max(IIf([Role]="Reader",[Role],Null)) AS Readers
FROM tblCoursePeopleRoles
GROUP BY Course, Person;

Add filter criteria as desired: Is Null / Not Is Null for the role fields.

[alpinegroove]

I will have to try this out tomorrow morning and see how it works.
Is it normal to need so many queries to accomplish something seemingly simple?
Is my database not designed or normalized properly?
Thanks.

[alpinegroove]

Yes, 3 roles are possible.

[alpinegroove]

What is the function of the MAX function here?

[June7]

The IIf expression restricts the field values to two possibilities - the specified role or Null. The Max in each expression works with the GROUP BY to return single record for each course/person pair. The Max for each pair in each field will be the role, if one exists. Without it, the records will not consolidate. Don't just take my word, experiment, try without it.

[alpinegroove]

I look forward to experimenting with it tomorrow. Thank you.
Is this preferable to using a crosstab query?
My junction table also contains salary information. Each Course, Person, Role combination has a different salary. So using a crosstab to consolidate, I can check which salary fields are null.
But I have been told that crosstab should be a last resort, and I am not sure why...

[June7]

It's because crosstabs are so dynamic. Depending on nature of the data and what is being pivoted, the columns can be different for each run. It is hard to design reports where the column names can change because the data may be filtered for a date range. Values in one period might not exist in another. If it is necessary to build reports on crosstabs, must be done very carefully or else have to rebuild every time because fields are no longer available for the bound textboxes and new fields need to be bound.

If you don't care about building a report or the crosstab could be stable enough to be report RecordSource, then go ahead and create crosstab. You might like it.

[alpinegroove]

I can't say I totally understand it, but it does work...
I thought Max could only be used with numeric values, not text...
Thank you!

[alpinegroove]

The IIf expression restricts the field values to two possibilities - the specified role or Null. The Max in each expression works with the GROUP BY to return single record for each course/person pair. The Max for each pair in each field will be the role, if one exists. Without it, the records will not consolidate. Don't just take my word, experiment, try without it.

I am now using the query you suggested to get instructor name as well:

Max(IIf([tblRoles.Role]="Instructor",[tblPeople.FirstName] & " " & [tblPeople.LastName],Null)) AS Instructor, Max(IIf([tblRoles.Role]="TA",[tblPeople.FirstName] & " " & [tblPeople.LastName],Null)) AS TA, Max(IIf([tblRoles.Role]="Reader",[tblPeople.FirstName] & " " & [tblPeople.LastName],Null)) AS Reader.

I just realized that I sometimes have a course that is co-taught by two instructors. My junction table can accommodate that, but that defeats the purpose of using the Max function.

Is there a way around this? I.e., if there are two instructors, list both of them under Instructors in the query?

[June7]

Your junction table shows names. Why does your Max expression refer to two tables? Are you actually saving a PeopleID but showed names in the data sample? You are doing these expressions in a query that joins tblPeople to tblCoursePeopleRoles?

Since the GROUP BY is on both course and person, both instructors will list. For example, add a record to your sample data
English 17 Susan Oats Instructor

The query will result in two lines for Susan Oats, one for each class she has a role in. Getting both instructors on one line for the same course is not possible with this method. At the point VBA code will probably be required.

[alpinegroove]

Your junction table shows names. Why does your Max expression refer to two tables?

I am using the Max expression in two ways: to get the names from tblPeople and to get salary information from the junction table, tblCoursePeopleRoles.

Are you actually saving a PeopleID but showed names in the data sample?

Yes.

You are doing these expressions in a query that joins tblPeople to tblCoursePeopleRoles?

Yes.

Getting both instructors on one line for the same course is not possible with this method. At the point VBA code will probably be required.

I think this is what I need. Any suggestions?

Sorry, as is clear, I am pretty new to this...

Thanks again for your help.

[June7]

Here is one way to concatenate records. Use the query built so far as the data source for the code.
http://allenbrowne.com/func-concat.html

[alpinegroove]

Thank you. I will take a look.