Has anyone successfully created a Bank Rec in Access?

[raziel3]

I'm trying to create a bank reconciliation in Access. Here's what I have so far.

Tables:
BANKSTMT - Uploads the Bank Statement to this table.
CHQREG - Keeps the listing of cheques issued, amount and cleared date
DEPOSITS - Records of Deposits made and date.

Queries (Grouped by Month End)
BANKRUNNING: Running totals of the BANKSTMT
GLRUNNING: Running totals of CHQREG and DEPOSITS
GLDepositTTL: Total Deposits
GLCHQIssued: Total Cheques Issued
GLCHQCleared: Total Cheques Cleared

Possible Report Format:

+--------------------------------------+----------------+
|            STATEMENT DATE            |   31-Oct-22    |
+--------------------------------------+----------------+
|                                      |                |
| BANK ENDING BALANCE AS PER STATEMENT |  11,047,635.70 |
|                                      |                |
| GL REGISTER                          |  10,706,231.40 |
|                                      |                |
| LESS: UNPRESENTED CHEQUES            |  771,155.89    |
|                                      |                |
| ADD: DEPOSITS IN TRANSIT             |  479,723.00    |
|                                      |                |
| LESS: BANK FEES                      |  49,971.41     |
|                                      |                |
| UNRECONCILED DIFFERENCE              |  (0.00)        |
+--------------------------------------+----------------+

Anyone have a template to get me started?

 

[gemma-the-husky]

Is it not working? I've never bothered, but it should work. The real issue is perhaps managing and auto matching the bank transaction descriptions with the company descriptions, as they are unlikely to match precisely. You also may store totals in the company records rather than individual transactions, which makes it harder to automate the reconciliation. It could be more trouble than it's worth.

 

[AngelSpeaks]

Many years ago, and I don't think I still have it, i created one. First the end date and beginning and ending balances were entered (I think, I may have saved the balance in a table). The outstanding checks were displayed and I put a check mark next to the cleared ones. Then I ran a report and update query. This was about 20 years ago.

 

[plog]

CHQREG - Keeps the listing of cheques issued, amount and cleared date
DEPOSITS - Records of Deposits made and date.

Credits and debits should go into the same table.

 

[CJ_London]

I open the bank statement as a .csv file in a query (could use a linked table) then link to the general ledger bank account filtered to exclude reconciled items and a descriptions table, typically matching on transaction type and amount plus description and a 'fuzzy date' (I use the actual date from the bank statement and a range of dates in the general ledger since these sometime don't match up - + or - 2 or 3 days on the bank date). Similarly the general ledger items listed start 3 days before the earliest statement date and 3 days after the latest statement date

Descriptions can be tricky but I include in the general ledger what I expect to appear as a description (e.g. 'chq 12343456' ) and it is 'self learning' - if a new description comes along, it is added when the record is reconciled. I have a separate table for alternative descriptions by transaction type.

Multiple matches remain unreconciled and are highlighted so the user can manually reconcile them.

Each bank statement has a reference (might just be bank name and statement date) and the reconciliation field is populated with this so there is a clear audit trail.

The reconciliation form has two subforms - one to list the unreconciled general ledger records and one to list the bank statement records plus a reconcilation difference calculation and controls to manage dates etc.