?
Solved

Bank Statement Reconciliations

Posted on 2002-07-12
7
Medium Priority
?
1,104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 7149012
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
ID: 7149386
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
ID: 7156841
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
Back Up Your Microsoft Windows Server®

Back up 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.

 

Author Comment

by:baggsh
ID: 7156853
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
ID: 7157172
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
ID: 7281359

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
ID: 7314818
This question will be placed in PAQ and points refunded.

Best regards,
ComTech
Community Support
Administrator @ EE
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

764 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