• 0

# SELECT SUM(Amount) FROM tblRepayments WHERE LoanID = [@field:LoanID] ........returns same number no matter the loanID ??

## Question

Hi All,

I have the below SQL statement:

SELECT SUM(Amount) FROM tblRepayments WHERE LoanID = [@field:LoanID]

tblLoans is the parent table for tblRepayments both linked by a LoanID field. Autonumber in tblLoans and Integer in tblRepayments.

The statements above should return a different value per LoanID as each loan has a different number of repayments and different repayment amounts.

however, the exact same number is returned each time.

Any help would be appreciated.

## Recommended Posts

• 0

Hello @Quinn,

Please use the 'target' keyword to get the correct result.

The statement should be     SELECT SUM(Amount) FROM tblRepayments WHERE LoanID = target.[@field:LoanID]

You may check an example in this article under the 'Subqueries in Calculated Fields' paragraph

The part of the statement after the equal sign is a dynamic one. So, a parameter is replaced by a real value when the DataPage is loaded. The parameter in your example is [@field:LoanID]

When the 'target' keyword is used the parameter is replaced with the value stored with this particular record.

In your example that will be:

SELECT SUM(Amount) FROM tblRepayments WHERE LoanID = 71 (for the 1st record)

SELECT SUM(Amount) FROM tblRepayments WHERE LoanID = 72 (for the 2nd record), etc.

Hope this helps!

##### Share on other sites

• 0

In addition to what @CoopperBackpack said, you can also use 'target.' not only on calculated fields but on calculated values as well.

When you are having doubt if your calculations is getting the proper field values, try putting that as a prefix.

##### Share on other sites

• 0

Hello! This link should also be helpful as you go along: https://howto.caspio.com/function-reference/

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.