Reconciling Multiple Tables

Giganews Newsgroups
Subject: Reconciling Multiple Tables
Posted by:  David Mitchell (david.smile.photograp…@gmail.com)
Date: Tue, 28 Jan 2020

I am looking into the possibility of reconciling tblBankStatement (list of =
bank account transactions via downloaded and linked .csv file) with various=
tables in my database. =20

tblcashpayments - (payments for non-account holding invoices)
tblaccountpayments - (payments from account holding customers)
tblacquisitions - (reconcile payments made to suppliers)
tblbanklodgements - (reconcile bank deposits made)

I have got as far as successfully importing the bank statement data into a =
new table (tblBankReconcile) and creating a "type" for each kind of transac=
tion.

For, example - I want to be able to select a number of transactions which h=
aven't been previously reconciled (1 or more) from tblacquisitions and a nu=
mber of transactions (1 or more,again not previously reconciled) from tblBa=
nkReconcile.  Then check that the total monetary value of the selected reco=
rds in tblacquisitions matches the total monetary value of the transactions=
selected in tblBankReconcile.  Assuming it does match then do something to=
record the fact they have been reconciled against each other (Junction tab=
le?) =20

Each reconciliation is between a number of transactions from tblBankReconci=
le and a number of transactions from just one of the other tables - i.e. th=
ere is never a scenario where you would need to select a record from more t=
han one table to reconcile against tblBankReconcile.

I hope this makes sense and I haven't waffled too much.  Been on some parti=
cularly strong painkillers and it may be affecting my brain function :-)

Replies