Calculate Total of Sorted SubTotaled Records

[dannyg]

We have a "Jobs" db that is keyed off of a "job number" field.  There can be multiple records for one job if there are chage orders/additions to the job.  Each record includes a "labor hours" field.  I am looking to calculate how many field hours we have left for all the open jobs.

 

I already have a report that does the subtotal of hours available, hours used, and hours remaining when sorted by "job number" and that works great.  I am trying to create a calculation to find the total of hours remaining with the twist that i want to exclude any jobs that have a sub-total of "hours remaining" that is negative.  

 

I know I could do a simple calculation of available hours - hours used but it won't give me a true result because there may be a sub totaled job where the hours used is over the hours available ( job went badly) which would skew my actual backlog or hours to be expended.

 

I have a dashboard set up and would like to drop this calculation on the dashboard to keep track of our current labor hour backlog.  Is there a way to have a calculation or script or relation ship to sort first then omit negative records so I can calculate the hours remaining.  I have tried a relationship to show only open jobs and that works but I can't get my head around how to sub sort them then calculate.

 

Any help appreciated.

[Fitch]

Could you be a little more specific about your tables and relationships? What is the table for the dashboard, and how does it relate to jobs?

Reporting via calculations on related records would be different than reporting via subsummaries on a found set.