Solved Totals Query

[ClaraBarton]

SELECT qryTransactions.TransactionID,
    qryTransactions.Cleared,
    Nz(-[Credit],0)+Nz([debit],0) AS CkAmount,
    -Nz([CatCredit],0)+Nz([CatDebit],0) AS CatAmount,
    qryTransfers.TAmount,
    Sum(IIf([Cleared]=True,+[CkAmount],0)) AS ClearedAmt,
    Sum(Nz([CkAmount],0)-Nz([CatAmount],0)+Nz([TAmount],0)) AS Remaining
FROM (qryTransactions LEFT JOIN tblCheckCat ON qryTransactions.TransactionID = tblCheckCat.fTransactionID) LEFT JOIN qryTransfers ON qryTransactions.TransactionID = qryTransfers.TTransID
GROUP BY qryTransactions.TransactionID,
    qryTransactions.Cleared,
    Nz(-[Credit],0)+Nz([debit],0),
    -Nz([CatCredit],0)+Nz([CatDebit],0),
    qryTransfers.TAmount;

I would like for Transaction19 to return 10 in the ClearedAmt and 10 in the Remaining which is what's happening in TransID 21.
What is the difference?

 

[Mark_]

Can you post the sample data that is driving this query?
You show the end results, but not the data itself.

 

[ClaraBarton]

Like this?

 

[ClaraBarton]

or this?

 

[DHookom]

You have CkAmount in a source as a column and then define it again in your query. You also reference CkAmount in two other expressions in your query. This creates an opportunity for confusion.

 

[CJ_London]

Without seeing the raw data , my guess would be you have two records with a transactionID of 19

 

[Mark_]

For your ClearedAmt, you don't need to do a SUM on it.
ClearedAmt: IIf([Cleared]=True,[CkAmount],0)

 

[ClaraBarton]

I removed the CkAmount from qryTransactions. If I remove the Sum from Cleared It says Does not include as a part of an Aggregated function. The only thing it will accept is sum. There are not 2 ID's 19. It is an autonumber.

 

[CJ_London]

There are not 2 ID's 19. It is an autonumber.

what about fTransactionID since this is what you are summing

 

[ClaraBarton]

Wow! You nailed it! It's a foreign key with nothing in it but it's in there twice. Thank you so much!