If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design to reconcile accounts
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? |
#2
|
|||
|
|||
Design to reconcile accounts
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. -- TedMi "Mike" wrote: 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? |
#3
|
|||
|
|||
Design to reconcile accounts
I would try a query. In the query, use the following statement in a query
field by itself: Reconciled:IIF([statementvalueone]=[statementvalue2],"yes","no") "Mike" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|