PDA

View Full Version : [SOLVED:] Calculated field based off sum of other fields


Cazca
02-24-2016, 01:45 PM
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
02-25-2016, 04:21 AM
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
02-25-2016, 11:13 AM
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
02-25-2016, 11:55 AM
I can't understand what you are trying to do.

You don't link a calculated value back into a table
Cazca
02-25-2016, 12:18 PM
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
02-25-2016, 01:48 PM
Your datasheet should be the query, because it displays the calculated value.

or a form control that looks up or calculates the value.
Cazca
02-25-2016, 01:56 PM
Understood. Thanks again jonh!