Solved

Bank Statement Reconciliations

Posted on 2002-07-12
7
1,055 Views
Last Modified: 2013-11-27
My bank provides the ability to download a Bank Satement from its internet site for the purpose of reconciliationand balancing the Bank Statement. When this link is clicked a text file is downloaded and when the download is complete the file associates itself with Excel and the appropriate icon appears when the file is viewed in the Windows explorer. I understand there is a commonly used concept which in prinnicple allows an automatic reconcilaition with the checks written in the accounting program on the local computer. I use an Access 2000 database which includes a Table called transactions. In the Transactions Table I have fields for check Number, Amount, a presented Y/N field and other fields. I am hoping to have an automated procedure which I imagine will include a form on which commands are placed to compare the similar fields in the Excel file and the Transactions table and place an "Y" in the "Presented" field in the Transactions Table wher the amount and check number appears in both. I wonder if anyone can give me some suggestions as to how all this should be setup and if possible any examples of how to do this.
Many thanks
 
0
Comment
Question by:baggsh
7 Comments
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
I would but Microsoft Money or Quicken as these support this feature and are designed to do this work for you, failing that it is a lot of programming to get the same4 functionality.

Cheers, Andrew
0
 

Expert Comment

by:kd0ec
Comment Utility
I wrote a simple GL program that has a check reconciliation feature in it. I have a "cleared" (Boolean) field and a cleardate (Date/Time) in my Trans table. I have a ckrec form that uses a query to populate it. The query's criteria is absence of a "check Mark" in the trans file.

If you use a simular method, you could import the excel file into a temp table indexed on the check number and look up these numbers from the ckrec form or the query itself. Once found, you could set the "cleared" field in you trans file and display the ones that haven't cleared.

You didn't mention whether the deposits are included in the excel file. If so, and the depostits are numbered, perhaps you could do the same with them.

kd0ec
0
 

Author Comment

by:baggsh
Comment Utility
kd0ec, how does one make a user friendly from to achieve all this...i.e. what sort of code would one use to import the excell file into a temporary table, then compare the two tables and set the cleared ones....can you give me more of an overview please...IN australia the deposits are not numbered unfortunately. Only an amount to compare which is a difficult issue and I can't see anyway around a manual comparison because the amount on the Bank Statement includes the sum of many different amounts in the Tarnsactions table. The nature of my work is that many people pay me funds during one day and at the end of the day I bank them as one total (payin) on the Bank...I'm not so worried about the deposits though and would appreciate some more help on the debits(checks) (which incidently in Australia we normally spell as cheques!!! In Australia we known as a wierd mob!
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:baggsh
Comment Utility
kd0ec, how does one make a user friendly form to achieve all this...i.e. what sort of code would one use to import the excell file into a temporary table, then compare the two tables and set the cleared ones and then delete the temporary table....can you give me more of an overview please...In australia the deposits are not separately numbered unfortunately. Only an total amount to compare  because the amount on the Bank Statement includes the sum of many different amounts in the Access Transactions table. The nature of my work is that many people pay me funds during one day and at the end of the day I bank them as one total (payin) on the Bank...I'm not so worried about the deposits though and would appreciate some more help on the debits(checks) (which incidently in Australia we normally spell as cheques!!! In Australia we known as a wierd mob!
0
 

Expert Comment

by:kd0ec
Comment Utility
This is a code snippet that I use to import a comma delimited text file into a table.

DoCmd.TransferText acImportDelim, _
                "Outages Export Specification", _
                "RawOutages", NewFile, False, ""

The "Outages Export Specification" was created by using File|Get External Data|Import then selecting the type of file (in this case txt) in your case xls. This will bring up a wizard which will allow you to define the fields and later, name the table in which the data goes. Do this first (before you automate the process) then what I do is make a macro to import the data, then compile the macro to get the "wizard's" code. You can experiment with the layout of your Excel spreadsheet. For example, you might need to copy the valid data to another "sheet" in Excel to avoid headers and so forth. Worst case you can design a macro in Excel that will export a comma delimited file limiting the export to only data in a range. Then import the comma delimited file into access.

kd0ec
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 

Accepted Solution

by:
ComTech earned 0 total points
Comment Utility
This question will be placed in PAQ and points refunded.

Best regards,
ComTech
Community Support
Administrator @ EE
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

15 Experts available now in Live!

Get 1:1 Help Now