Design to reconcile accounts

[Mike]

I am currently working on a project that will reconcile internal
company records for multiple locations with the bank statements. We
have txt files that are being imported to an access database for both
the internal accounts and the bank accounts. I have tables set up for
both but am not sure how to go about reconciling them. For example,
each store makes several daily deposits to the bank which may or may
not show up on the day of the deposit. I want to be able to compare
the two tables and then if the deposits match, mark them both
reconciled. Is the best way to do this to just add a reconciled yes/no
data type to each table or is there a better design?

 

[tedmi]

A cardinal rule of databases is that each piece of information be stored ONCE
AND ONCE ONLY. If you have "reconciled" information in two tables, then there
is the possibility that deposits could be marked reconciled in one but not
the other. My suggestion is to mark reconciliation only in the internal
table, not the bank table. To check for deposits that appear on the bank
statement but not on the internal table, run an Unmatched query on the bank
table.

 

[Golfinray]

I would try a query. In the query, use the following statement in a query
field by itself:
Reconciled:IIF([statementvalueone]=[statementvalue2],"yes","no")