Field Calculating based off sum of other fields

[Cazca]

This is likely very easy for you experts to solve.

I have two tables.

Table1 has purchase agreements with a limit threshold on money to be billed against it. The agreements are defined by a two label ID (agreement & subagreement; see below example). Table2 list each invoice that places a purchase against that purchase agreement.

How do I get a field in Table1 to add all the fields in Table2 that correspond to a record in Table1 and then subtract from a value in Table1? See below for example:

Table1

AutoNum	AGREEMENT	SubAgreement	DollarValue	DollarValueLeft
1	PA1	sa1	$10
2	PA1	sa2	$15
3	PA2	sa1	$20	X
4	PA2	sa2	$25

Table2

AutoNum	InvoiceNum	AGREEMENT	SubAgreement	AmountInvoiced
1	1234	PA2	sa1	$7
2	5678	PA2	sa1	$12

For an example, where X is, I need 'DollarValueLeft' to equal the sum of all 'AmountInvoiced' values in Table2, that equal PA2 and sa1 and subtract that from 'DollarValue' on Table1.



So for PA2-sa1, right now X should equal $1.

Thoughts?

[CJ_London]

something like

SELECT T1.*, T1.DollarValue-T2.sumAmount AS DollarValueLeft
FROM Table1 AS T1 INNER JOIN 
      (SELECT Agreement, SubAgreement, Sum(AmountInvoiced) AS sumAmount FROM Table2 GROUP BY Agreement, SubAgreement) AS T2 
          ON T1.Agreement=T2.Agreement AND T1.SubAgreement=T2.SubAgreement

[Cazca]

I'll give that a shot today, thanks man!

[Cazca]

Where would I put the code?

Not in the expression builder for a calculated field, correct?

[bcmarshall]

Paste it into the SQL view of a Query design.