Hello All, I am looking for any advice on the following dilemma. I have a table with new monetary transactions that come in every day. I would like to have the transactions(one debit and one credit) that net to zero eliminated from the table automatically. What I was thinking was to have an abs amount column added and run the duplicates query as a delete query based on this column, but that would not only eliminate the amounts that net to zero...but similar debit and credit amounts. Does anyone have any thoughts on the most efficient way to do this? Im sure the answer is staring at me in the face, but I cant get my head around it. Thanks in advance!
Eliminate records that net to zero
- Thread starter BWAT
- Start date
- Local time
- Today, 23:32
- Joined
- Sep 12, 2006
- Messages
- 16,121
what other data match, on the transactions that balance out - ie how do you relate the blaancing transactions to one another?
The data has already been filtered by this point so any transactions that balance eachother can be eliminated. The point of this is that an account gets hit several times a day, but must net to zero...so I am looking for any outstanding amounts
- Local time
- Today, 23:32
- Joined
- Sep 12, 2006
- Messages
- 16,121
do you mean that all transactions on a given account net to zero, or do you mean that a particular Dr and Cr net to zero
if the latter, then if you have two DRs of £50 each and 1 CR of £50 how do you know which DR matches with the CR.
if the former then its easy - do a totals query by account, and just select those accounts for which the total <>0
then use this query with the initial data, and just select the non-zero accounts
if the latter, then if you have two DRs of £50 each and 1 CR of £50 how do you know which DR matches with the CR.
if the former then its easy - do a totals query by account, and just select those accounts for which the total <>0
then use this query with the initial data, and just select the non-zero accounts
