Query "Sum"

[Bob Vance]

If my query has these 2 fields in it "Total Due" and "Paid Amount" what
expression do I use to get "Total Due" Minus "Paid Amount"

 

[Klatuu]

You use a Calculated Field in your query. In the Query builder where you
put the name of the field, you make up a name followed by a colon, then you
put in the expression. It would look like this:

AmountRemaining: [Total Due] - [Paid Amount]

The name AmountRemaining is only an example. Use any name you want.

 

[Bob Vance]

Oops Got it
Payable:
Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0)

 

[Bob Vance]

Because TotalDue is an expression of a sum that is creating a problem
Thanks Bob

SELECT tblInvoice.OwnerID, Sum(tblInvoice.OwnerPercentAmount) AS TotalDue,
qPaidAmountForPayment.PaidTotal, Max(tblInvoice.InvoiceDate) AS
MaxOfInvoiceDate
FROM tblInvoice LEFT JOIN qPaidAmountForPayment ON tblInvoice.OwnerID =
qPaidAmountForPayment.OwnerID
GROUP BY tblInvoice.OwnerID, qPaidAmountForPayment.PaidTotal
ORDER BY tblInvoice.OwnerID;

You use a Calculated Field in your query. In the Query builder where you
put the name of the field, you make up a name followed by a colon, then
you put in the expression. It would look like this:

AmountRemaining: [Total Due] - [Paid Amount]

The name AmountRemaining is only an example. Use any name you want.

If my query has these 2 fields in it "Total Due" and "Paid Amount" what
expression do I use to get "Total Due" Minus "Paid Amount"

 

[Klatuu]

Glad you got it working, but in the future, you get more accurate answers if
you include all the information in the original post.

Because TotalDue is an expression of a sum that is creating a problem
Thanks Bob

SELECT tblInvoice.OwnerID, Sum(tblInvoice.OwnerPercentAmount) AS TotalDue,
qPaidAmountForPayment.PaidTotal, Max(tblInvoice.InvoiceDate) AS
MaxOfInvoiceDate
FROM tblInvoice LEFT JOIN qPaidAmountForPayment ON tblInvoice.OwnerID =
qPaidAmountForPayment.OwnerID
GROUP BY tblInvoice.OwnerID, qPaidAmountForPayment.PaidTotal
ORDER BY tblInvoice.OwnerID;

You use a Calculated Field in your query. In the Query builder where you
put the name of the field, you make up a name followed by a colon, then
you put in the expression. It would look like this:

AmountRemaining: [Total Due] - [Paid Amount]

The name AmountRemaining is only an example. Use any name you want.

If my query has these 2 fields in it "Total Due" and "Paid Amount"
what expression do I use to get "Total Due" Minus "Paid Amount"

 

[Bob Vance]

I had to make a new query because one of the fields was a sum

SELECT qTotalDueForPaymentOne.OwnerID, qTotalDueForPaymentOne.TotalDue,
qTotalDueForPaymentOne.PaidTotal, qTotalDueForPaymentOne.MaxOfInvoiceDate,
[TotalDue]-[PaidTotal] AS AmountRemaining
FROM qTotalDueForPaymentOne;
Thanks Klatuu

Glad you got it working, but in the future, you get more accurate answers
if you include all the information in the original post.

Because TotalDue is an expression of a sum that is creating a problem
Thanks Bob

SELECT tblInvoice.OwnerID, Sum(tblInvoice.OwnerPercentAmount) AS
TotalDue, qPaidAmountForPayment.PaidTotal, Max(tblInvoice.InvoiceDate) AS
MaxOfInvoiceDate
FROM tblInvoice LEFT JOIN qPaidAmountForPayment ON tblInvoice.OwnerID =
qPaidAmountForPayment.OwnerID
GROUP BY tblInvoice.OwnerID, qPaidAmountForPayment.PaidTotal
ORDER BY tblInvoice.OwnerID;

You use a Calculated Field in your query. In the Query builder where
you put the name of the field, you make up a name followed by a colon,
then you put in the expression. It would look like this:

AmountRemaining: [Total Due] - [Paid Amount]

The name AmountRemaining is only an example. Use any name you want.


If my query has these 2 fields in it "Total Due" and "Paid Amount"
what expression do I use to get "Total Due" Minus "Paid Amount"