Find duplicates based on a negative and positive number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK, I have 1 table that contains records where the will be dollar values
that will need to be matched. But, The dollar amount in one record will be
a negative of the dollar amount in the matching record. i.e.
Record 12345 $5.75
Record 23456 -$5.75

There is other data that may or may not match.

Thanks,
 
Sounds like your structure is flawed. What if you have two records that are
$5.75 and two that are -$5.75? This sounds like an accounting schedule to
me. The Record number would be the reference number, but you don't seem to
have a control number.

For example....

When Invoiced...

Reference: 7778
Vendor: A123
Invoice: 4567
Amount: $5.75

When paid...

Reference: 7779
Vendor: A123
Invoice: 4567
Amount: -$5.75




Your reference will be unique, but you still have to apply it to a
particular control number.

Another example might be posting and selling a vihicle. The references
would vary, but the vehicle stock number of VIN number would still be in
both records.

I'd suggest looking at your data structure and figuring out what's missing.
Not sure how else you will be able to maintain this database as the records
increase and duplicate amounts appear.
 
You are correct Rick, this is an accounting issue. Basically, the reference
number would be the invoice #, but if the item was a return, the return
number may be used instead of the reference number. The reference number in
your example is a sequentially numbered field that is a line number in the
application.
 
Sounds like a balance forward schedule. Do you have a customer number? If
it is balance forward instead of open item, then you do not need to apply
each item to a particular invoice, just to the client in general.

balance forward....

Customer: 7726262
123456 15.00 (invoice)
CR222 5.00- (credit)
999972 10.00- (payment)




Open Item:

Customer: 7726262
88899 123456 15.00
88900 123456 5.00- (partial payment)
88901 123456 5.00- (credit)
88902 123456 5.00- (partial payment)
 
Back
Top