Reconciliation for two bank systems, with one system paying aggregate fees of another.

I have two systems that I want to reconcile in MS Access.  I have created two tables each representing a system, DART and CAS.  DART shows the amount of money that should be paid out and CAS shows the actual movement of the cash.  Next to every entry in the DART table I have a check box.  If that same money is seen in CAS for that same account an update query checks the checkbox.  This works the other way too, so within the CAS table there is a checkbox next to every entry and the box is checked off if the same money for the same account is seen in DART.  

DART has auctions, either weekly or monthly, and one money in each of these auctions is a fee called an agency fee.  The agency fees will be paid out in CAS as either one-to-one or multiple fees, up to six aggregate fees.  I have created a way to check off the one-to-one fees.  Some of the fees in DART can be the same, so if I have two $100 fees in DART and then I see a $100 fee paid out in CAS, I will make the query check off the $100 fee in DART with the earliest date.  The problem I have is being able to reconcile the multiple fees.

Example of a weekly auction:

11/1/03    $100
11/8/03    $100
11/15/03   $200
11/22/03   $150
11/29/03   $150
12/06/03   $150

I do not know when CAS will pay out these fees and with what type of multiple of these fees.  If I see $100 go through CAS, an update query will check off the 11/1/03 $100 in DART.  But I can also see $200(100+100), $400(100+100+200), $550(100+100+200+150), $700, or $850 go through CAS.  If I saw the $550 go through CAS I would want to check off the 100,100,200 and 150(11/22/03) in DART with an update query.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm afraid that what you are trying to do is logically impossible unless you can introduce further matching rules.

Suppose you have:
on one side and 300 on the other.
On what basis would you decide what the 300 related to.
Whichever you choose I can give you a next value to prove you wrong.

In my experience with accounting systems ,this is where you present the transactions to the user and ask them to match manually.


jadedataMS Access Systems CreatorCommented:
Hey andy246!

  peter57r is correct about getting some additional matching rules.
  Do you have some kind of identifier that qualifies what type of transaction either is assigned to?

andy246Author Commented:
For Pete’s example, as a rule I will not see $300 come in on the CAS side.  The CAS payment will be aggregate DART fees in ascending order.  The figures that I should see in CAS are $100, $250(100+150), $350(100+150+100), $500(100+150+100+150), $600, or $650.  

Say I saw a payment of $250 in CAS.  The $250 should match off with the earliest entries (minimum date) that add up to $250.  In this case, the $250 would match up to the first 100 and the first 150 listed.

Receiving a payment of $300 in CAS would be more an exception than the rule.  I might want this to show as an error since $300 should not be the amount paid out.  If I needed to show it marking off DART payments I might want to mark off the earliest entries that match up to $300 (the first 100, the first 150 and then the 50).  But again, seeing $300 would be more the exception than the rule and I generally should not see it appear.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

jadedataMS Access Systems CreatorCommented:
Using that logic, one would tend to believe that it didn't matter which debit(s) matched to which credit, as long as the aggregate total was a match.  Is that correct?
andy246Author Commented:
Jack- that is correct. And if there is more than one aggregate total that matches, the amounts with the earliest dates that make up an aggregate total should be checked off.  Following the first set of numbers in Jack's example (excluding the 300), if $250 comes into CAS, the FIRST $100 and the FIRST $150 should be checked off.

jadedataMS Access Systems CreatorCommented:
I'm thinking of a routine something like this:  (replace <SubName> with whatever you like)

Public Sub <SubName>
  dim rsCAS as recordset
  dim rsDART as recordset

  set rsCAS = currentdb.openrecordset("SELECT * FROM CAS WHERE (not [Cleared]);",dbopendynaset)
  set rsDart = currentdb.openrecordset("SELECT * FROM DART WHERE (not [Cleared]);",dbopendynaset)

  do until rsCAS.eof
    targetamount = rsCAS("Amount")

     if rsDART("Amount")<= targetamount
       targetamount = targetamount-rsDART("Amount")
       rsDART("Cleared") = true

    if targetamount>0 then
      goto subName_ClearTarget:


  on error resume next
  rsDART.close: Set rsDART=nothing
  rsCAS.close: Set rsCAS=nothing
  exit sub

  select case err
  case else
    msgbox Error$
    resume <SubName>_Exit:
  end select

end sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andy246Author Commented:
Note:  I just joined experts exchange a few days ago.  I just read the guidelines and learned a few things.  Apologize for taking so long to get back to my own question, I was out for a couple of days.  In terms of point value I don&#8217;t know if I should have put it so high, but it seems like it only really hurts me. There are a few questions to follow all related to the same code that I am trying to write.  I have awarded most of my points here so I want to see if I can make the most of it.

I tried Jack&#8217;s code and it is definitely putting me in the right direction.  I needed to tailor it somewhat to take into account other things I needed which I had not clearly mentioned before.  I made two new queries which are built off the original CAS and DART tables and changed the code to run off these queries instead of running off the DART and CAS tables.  I am only using this code for certain items within the tables.  On the CAS side I am using it for items with the description &#8220;payment of billing invoice&#8221; and on the DART side the description is &#8220;fee&#8221;.  I also included the date on the CAS side in my CAS query because I will be taking in DART and CAS feeds daily.  Each day I will put the CAS date in but the CAS &#8220;payment of billing invoice&#8221; will match to ALL DART &#8220;fees&#8221; excluding the past seven days (If the CAS feed is for November 12th I want to match to all DART fees before November 5th).  The code also works in terms of the order of the records and not the date of the records.  In the DART query I just made I put the dates in ascending order and the related records in the DART table then get checked starting from the earliest date.

Problems I have:
1) Every CAS and DART entry has an account #.  If I get a CAS &#8220;payment of billing invoice(PBI)&#8221; for account 012345 I need to run the code for this item for only DART fees in account 012345. I tried making this equal in my CAS query but this did not work.

2) I want to prompt the user for the date.  In my CAS query that the code corresponds to I wrote [x] for the criteria under the Date field.  I have done this for other queries and the user is prompted to fill in the criteria and can then fill in the date.  For some reason this did not work.

3) I also want to check off the CAS side of the equation, not just the DART side.  This is probably not complicated, but I am not sure exactly where to write in the code (I&#8217;m new to Access)

4) The numbers I am dealing with here are all negative numbers.  They represent debits being paid out.  I had been testing the code with positive numbers and then realized that I should be testing with negative numbers.  When I tested with negative numbers the code did not work.  I then changed in both my CAS and DART queries the Amount to &#8211;[Amount].  The DART table still got updated so I thinks this works okay but I will have to test with more information to be sure.   Does this seem like it should work correctly?

5) When the CAS &#8220;PBI&#8221; matches against DART &#8220;fees&#8221;  the past seven days of DART fees should be excluded from the code

6) If the CAS &#8220;PBI&#8221; does not match off with the DART entries I&#8217;d like to display a message that says so.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.