Reconciliation for two bank systems, with one system paying aggregate fees of another.
Posted on 2003-11-10
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:
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.