How to Use Access for Bank Reconciliation
By Tiesha Whatley
Microsoft Access 2007 is a simple, yet powerful, database application that you can use to keep up with your business or personal data. Use it to maintain a database for customer service, business and personal contacts, inventories, home DVD collections and bank reconciliations. A bank reconciliation database is easy to create; you only need a couple of tables, one form and a report for viewing your table items. After you create the database, you can use it to balance your bank accounts month after month.
Open Microsoft Access 2007 and click "Create New" and then "Blank Database" to create a new database.
Click the "Create" tab then the "Table Design" button in the "Tables" group. A new table will open in "Design View." Type "Transaction Number" in the "Field Name" column of the first row. Click the drop-down arrow under "Data Type" and choose "AutoNumber." Move to the second row and type "Type" and choose "Text" as the "Data Type." Go to the third row and type "Check Reference Number" and choose "Number" as the "Data Type." For the fourth row, type "Amount" and choose "Currency" as the "Data Type." In the fifth row, type "Cleared" and choose "Yes/No" as the "Data Type." Click the "Save" icon on the toolbar to save the table as "Register." Close the table.
Click the "Create" tab and then "Table" in the "Tables" group. A new table will open in "Datasheet View." Type "Check" in the first column of the first row. Type "Deposit" in the first column in the second row. Type "Withdrawal" in the first column of the third row. Type "Debit Card" in the first column of the fourth row and "Automatic BillPay" in the first column of the fifth. Click the "Save" icon and save the table as "Transaction Type." Close the table.
Open the "Register" tab by double-clicking in the "Navigation Pane" at the left side of the window. Click "Design View" at the top of the window when the window opens. Click the row for "Type" to select the row. Using the "Properties" window at the bottom of the screen, click the "Lookup" tab. Click "Combo Box" in the "Display Control" area. In the "Row Source Type" box, select "Table/Query." Select the "Transaction Type" table in the "Row Source" box. Save and close the table.
Click the "Create" tab and then click "Form" in the "Forms" group. Select the "Register" table. Click "OK" until "Finish" appears. Click "Finish" to completely create the form based on the "Register" table. The "New Form" wizard places the fields on the form that correspond with the table. Open the form and enter the details for the bank records you intend to save in the "Register" table. Close the form when you have completed entering all the details.
Click the "Create" tab and then click "Report Wizard" from the "Reports" group. Choose the "Table: Register" in the "Tables/Queries" section. Click the ">>" button to add the fields from the table to the report. Click the "Next" button. Choose a design for the report and then click "Next." Choose how you want the items on the report sorted and then click "Next." Finally, click "Finish." View the report when you want to see all the items in your "Register" table in a report style to reconcile them with the statement from your bank.
References
Writer Bio
Tiesha Whatley has been writing for over 10 years. She has been published in "Marie Claire," "Ebony" and "Modern Bride" magazines. She holds a Bachelor of Science in English from the University of Alabama at Birmingham and has been working in the wedding planning industry for over 13 years.