Jump to content
VirtualBob

Finding date and amount matches

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

By using this site, you agree to our Terms of Use.