I am trying to create some reports using our invoice history files. The database has two nearly identical records. The first is an "I"nvoice record (same customer number, same apply-to number) and the second is a "P"ayment or "C"redit, with the same customer no. and same apply-to but the dollar amount is exactly opposite (-12.34 vs. 12.34). The two (or more, in the case of multiple partial payments) records net to zero.
I can easily find the duplicates or use an aggregate query and find the records that net to zero, but how do I delete both records? If the item is paid, I want to kill both records - so that it doesn't clog up the tables and slow everything down. I only want to work with open, unpaid invoices.
Creating a delete query by matching the data table with the summation query won't work - the records are always locked and won't allow deletion.