Ignore Transactions that Balance to Zero

[dashiellx]

I have a table that lists transactions for encounters. For example:





As TransactionID 40717 balances out TransactionID 48355, so those should not be returned.

I guess I need a query that will return the transaction ID of any transactions that balance out to Zero. Then I can look for transactions that are not in that query.

Having trouble wrapping my brain round this one. I also posted this to UA. but have been unsuccessful there.

[June7]

Virtually impossible. The only thing exclusively relating those two records is the amount and other records not shown could have that same amount.

This is a rather usual requirement. Conventional approach would be just to calculate the net amount with an aggregate query.

[dashiellx]

With assistance, I was able to get this working:

SELECT *
FROM (SELECT tblTechnicalDenialAdjustments.TechnicalDenialAdjustmentID, tblTechnicalDenialAdjustments.TransactionAlias, tblTechnicalDenialAdjustments.Encounter, tblTechnicalDenialAdjustments.TransactionAmount, tblTechnicalDenialAdjustments.ActivityDate
 FROM tblTechnicalDenialAdjustments)  AS sqry 
INNER JOIN (SELECT tblTechnicalDenialAdjustments.TechnicalDenialAdjustmentID, tblTechnicalDenialAdjustments.TransactionAlias, tblTechnicalDenialAdjustments.Encounter, tblTechnicalDenialAdjustments.TransactionAmount, tblTechnicalDenialAdjustments.ActivityDate FROM tblTechnicalDenialAdjustments)  AS sqry2 
ON (sqry.TransactionAmount = (sqry2.TransactionAmount*-1)) AND (sqry.Encounter = sqry2.Encounter);

This appears to be returning the correct records. I will need to do some additional verification, but I think I'm at least on the right track.