Subtracting multiple fields from a total

[Pete_Lindley]

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?

[kuovonne]

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.