Solved

Bank Statement Reconciliations

Posted on 2002-07-12
7
1,079 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
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
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
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IT Desktop Support 11 74
unable to create table-based data macro in MS ACCESS 2013 11 25
User Level Security 6 38
Visible ppty on rpt 7 7
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

803 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