Help on getting totals queries in Access 97 1

[Jorgandr]

I have a table callled
classdate which contains these fields

dateid
classid
instructor1
instructor2
instructor3
instructorcredit

There can be as many as 3 instructors for one class on any date.
One instructor could teach the same class just on different days.
I wanted to develop a query that summed up the total number of instructorcredits for each employee

the conditions are as follows:
instructor1, instructor2, instructor3 = EmployeeNumber
Only total unique classes (ClassID) no matter what instructor they (1, 2, 3)

Example:

classid = 1
dateid = 1 (date of the class)
instructor1 = 100 (EmployeeNumber)
instructor2 = 101
instructor3 = 102
instructorcredit = 1

classid = 1
dateid = 2
instructor1 = 101
instructor2 = 100
instructorcredit = 1

classid = 1
dateid = 3
instructor1 = 102
instructorcredit = 1

classid = 2
dateid = 4
instructor1 = 100
instructorcredit = 1

so in this example:
total credits for Employee (100) = 2 (1 for classid = 1, and 1 for classid = 2)

total credits for employee (101) = 1 (1 for classid 1)

My question is then, how do i get a query that will show the listing of each employee number, along with the total number of instructorcredits following the criteria above. Thank you for any help.

 

[rac2]

You can do this in two stages. Make a UNION query to re-arrange the three instructor columns into one column. Then make an aggregate query to summarize the credits by instructor. In Access you can use the menus and wizards or you can type the SQL into the SQL view directly. Here are the queries.

SELECT instructor1 AS "Instructor",
       instructorcredit
FROM classdate

UNION

SELECT instructor2,
       instructorcredit
FROM classdate

UNION

SELECT instructor3,
       instructorcredit
FROM classdate

You might want to add a condition to each SELECT statement to eliminate rows without a value for the instructor.

Save that query and call it InstructorCredits

SELECT Instructor,
       SUM(instructorcredit) AS "TotalCredits"
FROM InstructorCredits
GROUP BY Instructor

 

[GoDawgs]

Just to help you going forward, you should not have Instructor 1, 2, 3 in that table, you should create a new table that just stores the class ID, date ID, and instructor ID (and the order-1, 2, 3-if that's necessary). Basically your class ID 1 and date ID 1 should look like this in the new table:

Class ID Date ID Instructor ID
1 1 100
1 1 101
1 1 102

This will help you greatly going forward as all you'd have to do to create the query you're asking for now is pull the data from this table (the instructors won't be spread over 3 fields). Just a suggestion, hope that helps.

Kevin

 

[lespaul]

Kevin's post is basically telling you that your table is not normalized. You need an intermediate table that stores the many-to-many relationship between classes and instructors. If possible, you should reconsider your table design.

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!