AnsweredAssumed Answered

Sum of fields based on secondary field

Question asked by TimothySlinks on Sep 14, 2011
Latest reply on Sep 14, 2011 by philmodjunk

Title

Sum of fields based on secondary field

Post

I have CLIENTS and PROJECTS tables. Each project is related back to a clientID.

Each Project contains three fields in question: 
proj_status
proj_billable_total
proj_gratis_total.

I want the CLIENTS table to have the following calculations:

Sum(PROJECTS::proj_billable_total) when proj_status="ACTIVE"
Sum(PROJECTS::proj_billable_total) when proj_status="INACTIVE"
Sum(PROJECTS::proj_gratis_total) when proj_status="ACTIVE"
Sum(PROJECTS::proj_gratis_total) when proj_status="INACTIVE" 

 

I can't find anything online that I can make sense of. Is this the job for a self-join? Do I need two new tables: Active Projects & Inactive Projects?

Can you get me started? THANKS!

Outcomes