Calculated field based off sum of other fields

[Cazca]

Any help is greatly appreciated!

I have two tables like so:

Tb1:

AutoNum	Agreement	SubAgreement	Value	ValueLeft
1	Agree1	Sub1	$10
2	Agree1	Sub2	$15	X
3	Agree2	Sub1	$8
4	Agree2	Sub2	$17

Tb2:

AutuNum	InvoiceNum	Agreement	SubAgreement	AmountBilled
1	1234	Agree1	Sub2	$4
2	5678	Agree1	Sub2	$3

My question is, how do I get X in Tb1.

The value of X should be "X = $15 - $4 - $3"

[ValueLeft] needs to look at Tb2 where [Agreement] and [SubAgreement] equals [Agreement] and [SubAgreement] in Tb1 and subtract all [AmountBilled] values from [Value].

These tables are displayed in a form.

[jonh]

2 queries


Sum values in tb2...


T2sum query

SELECT
Agreement,
SubAgreement,
Sum(AmountBilled) AS SumOfAmountBilled
FROM Tb2
GROUP BY Agreement, SubAgreement;

get result

SELECT
Tb1.AutoNum,
Tb1.Agreement,
Tb1.SubAgreement,
Tb1.Value,
[Value]-[SumOfAmountBilled] AS ValueLeft

FROM Tb1
LEFT JOIN T2sum ON
(Tb1.SubAgreement = T2sum.SubAgreement) AND
(Tb1.Agreement = T2sum.Agreement);

[Cazca]

Works perfectly. Really appreciate the help you've been giving me.

The second query is named GetResult.

I would like it so that users do not have to run the query to get ValueLeft.

When I try to turn ValueLeft on Tb1 into a Lookup field and then refer to the query GetResult, I get the error: "No valid fields can be found in 'GetResult'. You may have selected a query that uses the table you're adding the lookup column to. Please select a new source."

[jonh]

I can't understand what you are trying to do.

You don't link a calculated value back into a table

[Cazca]

In the datasheet view of the GetResult query, ValueLeft displays... well... the value remaining. I need this value displayed on Tb1 and associated with the correct record.

The more I think about, the more I'm sure it's not possible...

[jonh]

Your datasheet should be the query, because it displays the calculated value.

or a form control that looks up or calculates the value.

[Cazca]

Understood. Thanks again jonh!