Subtracting multiple fields from a total

Here’s one for you.
We have a purchase order for a job, with a total amount in one table.

We have multiple work shifts each be a varied amount depending on hours and rates of pay in another table.

I’d like to be able to summarise all shifts and subtract them from the total purchase order to check we are under budget.

Is this possible?

Yes, this is possible, assuming that everything is joined with linked records.

I am picturing the following tables:

  • Job/Purchase Order (linked to Shifts)
  • Shifts (linked to Job/Purchase Order and Pay Rate)
  • Pay Rates
  1. In the Shift table, rollup the Pay Rate.
  2. Have the Shift calculate its amount based on its hours and pay rate.
  3. In the Job/Purchase Order, rollup the shift amounts. (SUM(values))
  4. In the Job/Purchase Order, subtract the rolledup shift amounts from the total.
1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.