K
Karl Burrows
I am working with a linked table using ODBC. The table is within our
accounting program (MRI/Intuit). The table consists of all loan balances
and transactions for the history of a bank loan for various entities. I
need the current loan balance for each loan in each entity for auditing and
compliance.
The issue is that MRI doesn't have a trial balance where I can just query
that table and pull my info into Access. It has a transactional table with
all related transactions for each loan. There are 4 key columns. The first
identifies the entity (like 091A, 207A, etc.). The second shows whether the
transaction is a balancing entry (where they close the books for each
period) or a transaction entry. They are marked as B for balance and N for
other transaction. The next column is transaction period and final column
is Activity.
The kicker here is it shows all transactions for this loan from inception,
so it would have to pull the most current balance entry first and the net
out the transactions since that last balance entry. It would also have to
check the date to make sure it is still an active loan so it doesn't try to
pull in a balance that may be inactive (I doubt this would happen, but just
trying to cover my bases!).
Any suggestions on how to build the query to pull in the current balance for
each entity? Thanks!
accounting program (MRI/Intuit). The table consists of all loan balances
and transactions for the history of a bank loan for various entities. I
need the current loan balance for each loan in each entity for auditing and
compliance.
The issue is that MRI doesn't have a trial balance where I can just query
that table and pull my info into Access. It has a transactional table with
all related transactions for each loan. There are 4 key columns. The first
identifies the entity (like 091A, 207A, etc.). The second shows whether the
transaction is a balancing entry (where they close the books for each
period) or a transaction entry. They are marked as B for balance and N for
other transaction. The next column is transaction period and final column
is Activity.
The kicker here is it shows all transactions for this loan from inception,
so it would have to pull the most current balance entry first and the net
out the transactions since that last balance entry. It would also have to
check the date to make sure it is still an active loan so it doesn't try to
pull in a balance that may be inactive (I doubt this would happen, but just
trying to cover my bases!).
Any suggestions on how to build the query to pull in the current balance for
each entity? Thanks!