Display total of contents of 'fields a' for records that have identica

[mjstone323]

Hi there - I couldn't fit all of the title in - The full subject is:

Display total of contents of 'fields a' for records that have identical 'fields b' in common.

I am attaching code for a query that grabs all the records in a few tables that relate to workshops that have been attended but not yet paid for. What I want to do with them next is to add the "WorkshopCost" field for each of the records that has an identical "SchoolName" and display the result, so that we have a subtotal for each school.

I suspect this should probably be very easy but for some reason I can't figure out how it should be accomplished - if there is another posting where this is answered I would be very happy is someone would point it out! or otherwise give me a shove in the right direction.

I'm thinking I need to do something with the array created by my query...?

 

[philhege]

You need an aggregate query that returns the sum of costs grouped by school name. I'm not sure that you can do this in the same query you attached.

Try

SELECT SUM(WorkshopCost) as UnpaidCost, s.SchoolName
FROM WorkshopRegistration as wr
	
	LEFT JOIN WorkshopEventSessions AS wes ON wr.WorkshopSessionID = wes.WorkshopSessionID 
	LEFT JOIN WorkshopEvent AS we ON wes.WorkshopEventID = we.WorkshopEventID
	LEFT JOIN Employee AS e ON wr.EmployeeID = e.EmployeeID
	INNER JOIN School AS s ON e.SchoolID = s.SchoolID
	INNER JOIN County AS c ON s.County = c.CountyID

WHERE  wr.WorkshopPaid Is Null
AND wr.WorkshopCompleted = 'True'
AND wes.WorkshopSessionCost <> 0
GROUP BY SchoolName

Phil H.
Some Bank
-----------
Time's fun when you're having flies.