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

Posted on 2003-11-10
Last Modified: 2008-07-03
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.


Question by:andy246
  • 3
  • 3
LVL 77

Expert Comment

ID: 9717616
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.


LVL 32

Expert Comment

ID: 9718076
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?


Author Comment

ID: 9718146
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.
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

LVL 32

Expert Comment

ID: 9718176
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?

Author Comment

ID: 9718340
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.

LVL 32

Accepted Solution

jadedata earned 500 total points
ID: 9718940
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

Author Comment

ID: 9737011
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.  

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question