Subtract value from one table from each item in a group in another table.

Hello!

I have a database to keep track of time spent on development work. The database uses mainly two tables: Estimates and Status.

The Estimates table holds a static number for each item to be worked on. We generally subtract this number from the total number of hours in Status spent on each of the items. In queries, to calculate the overall delta, we subtract the Estimate from the overall Status for each item.

However, we would like to create a report that gives us a running total for each item. So, if we have 100 hrs in the Estimate table for Item A and 5 hrs for item B, then the report would ideally show something like this (delta between Status table and static value in Estimate table):

Code:

---

Item | Resource Name | Estimate | Actual | Delta
--------------------------------------------------------
 A      John Doe            100        10        -90
 A      Jane Doe            90          5          -85
 A      John Appleseed    85          5          -80
 B      John Doe            5            10          5
 B      Jane Doe            -5          5          10

---

This is so that we can see how our actual hours spent working on a task line up to our estimates. So, if we are under estimating our work, we can easily see this.

In Excel, this is of course no issue, but it becomes an issue when trying to write a query in Access to report this information.

As I said, we can do the overall numbers, just not the line item numbers. Any help on this would be MUCH appreciated.

Thanks.

Subtract value from one table from each item in a group in another table.