Finding date and amount matches

[VirtualBob]

I've imported some data from various banking accounts for monitoring (just my accounts), but somehow over time some date-entries have been omitted. As I have commented on a lot I don't want to redo a raw import but identify what's missing. So I have a commented database that has date, debit and credit fields. I also have raw output (from online banking) databases that have the same fields.

At the moment I've created 'missing data' database to import into that has a calculation field that adds together date&debit&credit fields that outputs as a number. Then I omit duplicates. It sort of works so far. Is there a more elegant/accurate way of doing this?

[comment]

5 hours ago, VirtualBob said:

a calculation field that adds together date&debit&credit fields that outputs as a number.

That's a very dangerous method, because you can easily identify two (or more) unrelated transactions as duplicates of each other - for example, a credit of $1,000 on January 1, 2017 will be seen as duplicate of a credit for $880 on May 1, 2017.

It would be much better to use a relationship with two match fields (one for the date and one for the calculated amount) to identify the duplicates. Even then you might have a problem if you happen to have two separate transactions for the same amount on the same day. Do you not have any other indicator besides the date and the amounts?

 

5 hours ago, VirtualBob said:

Then I omit duplicates.

I am not sure you need to do that. You could have separate tables for the "full" list of transactions and the one with the comments, and then just copy the comments over to the "full" list, using the above-mentioned relationship.

[VirtualBob]

Thanks for that - I knew it was a bit rough so this is what I was looking for. I'm not working blind so I still manually check for errors.