Solved

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

Posted on 2003-11-10
7
367 Views
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.

Thanks,
Andy











































































0
Comment
Question by:andy246
  • 3
  • 3
7 Comments
 
LVL 77

Expert Comment

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

Suppose you have:
100
150
100
150
100
50
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.

Pete



0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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?

regards
Jack
0
 

Author Comment

by:andy246
Comment Utility
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.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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?
0
 

Author Comment

by:andy246
Comment Utility
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.

Andy
0
 
LVL 32

Accepted Solution

by:
jadedata earned 500 total points
Comment Utility
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")
    rsDART.movefirst

<SubName>_ClearTarget:
     if rsDART("Amount")<= targetamount
       targetamount = targetamount-rsDART("Amount")
       rsDART.edit
       rsDART("Cleared") = true
       rsDART.update
    endif
    rsDART.movenext

    if targetamount>0 then
      goto subName_ClearTarget:
    endif

    rsCAS.movenext
  loop

<SubName>_Exit:
  on error resume next
  rsDART.close: Set rsDART=nothing
  rsCAS.close: Set rsCAS=nothing
  exit sub

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

end sub
0
 

Author Comment

by:andy246
Comment Utility
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.  
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now