Can access calculate balances like excel?

[Carmen]

I am new to using code in access. What I want to do is set up a table that
keeps track of the debits and credits of a loan showing the balance of the
total loan on each entry. In excel this is simple you could create a formula
that takes the previous row balance + debit - credit = current balance; (loan
accounting). The next row will use the same formula and create a new balance
that does not affect the previous row balance. This is what I want the
access table(form) to show. Can I do this using vba code, queries,
expressions??????

The aim of the database is to track the loan balance through the loan
payments made, interest, and repayments as the transactions occur. I want to
use this to create a statement at the end of each month. I know the report
option can use a running sum but I want the user to see the balances as they
occur while using the form.

 

[PC Datasheet]

In Access loan accounting is set up with a Transactions table that looks
like:
TblTransaction
TransactionID
TransactionDate
TransactionAmount
TransactionType

TransactionType is a combobox with values of "Debit" and "Credit".
TransactionAmount is recorded as positive for Debit and negative for Credit.
CurrentBalance is calculated in a query as the sum of TransactionAmount of
all previous TransactionDates.

 

[gls858]

I am new to using code in access. What I want to do is set up a table that
keeps track of the debits and credits of a loan showing the balance of the
total loan on each entry. In excel this is simple you could create a formula
that takes the previous row balance + debit - credit = current balance; (loan
accounting). The next row will use the same formula and create a new balance
that does not affect the previous row balance. This is what I want the
access table(form) to show. Can I do this using vba code, queries,
expressions??????

The aim of the database is to track the loan balance through the loan
payments made, interest, and repayments as the transactions occur. I want to
use this to create a statement at the end of each month. I know the report
option can use a running sum but I want the user to see the balances as they
occur while using the form.

Access and Excel are two entirely different animals. Each program has a
specific purpose. Some things simply don't need to be done in a data base.
You say "a loan" this typically wouldn't be something you do in Access.
To me this sounds like something more suited to Excel.
Now if you have large number of loans to track Access might be the
right program.

just my 2 cents

gls858

 

[Vincent Johns]

I am new to using code in access. What I want to do is set up a table
that keeps track of the debits and credits of a loan showing the
balance of the total loan on each entry.

[...]

Access and Excel are two entirely different animals. Each program has a
specific purpose. Some things simply don't need to be done in a data base.
You say "a loan" this typically wouldn't be something you do in Access.
To me this sounds like something more suited to Excel.
Now if you have large number of loans to track Access might be the
right program.

just my 2 cents

gls858

You might also consider a hybrid approach. Although Access can do the
calculations, it's probably easier to leave them in Excel, and link an
Access Table to that table in Excel. After all, your Excel version is
already working, isn't it? You might define some Queries in Access to
pull out the data that you need to include in your Access Reports.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Carmen]

Thanks for your help. I am a little confused still though. How does the
TransactionAmount get recorded as positive or negative.

For an example, would it be something like
If TransactionType = Debit then
TransactionAmount = Negative
else if TransactionType = Credit then
TransactionAmount = Positive

CurrentBalance query, how do I sum the TransactionAmount of all previous
TransactionDates.

Sorry to be a pain.

Carmen

 

[Carmen]

Thanks very much for that. There are several loans to keep a track of. How
would I go about the Hybrid option. Excel is working but I am not sure how
I would run queries from access with excel information. Could you advise? I
would greatly appreciate any help you could offer. Thanks again.

Carmen

I am new to using code in access. What I want to do is set up a table
that keeps track of the debits and credits of a loan showing the
balance of the total loan on each entry.

[...]

Access and Excel are two entirely different animals. Each program has a
specific purpose. Some things simply don't need to be done in a data base.
You say "a loan" this typically wouldn't be something you do in Access.
To me this sounds like something more suited to Excel.
Now if you have large number of loans to track Access might be the
right program.

just my 2 cents

gls858

You might also consider a hybrid approach. Although Access can do the
calculations, it's probably easier to leave them in Excel, and link an
Access Table to that table in Excel. After all, your Excel version is
already working, isn't it? You might define some Queries in Access to
pull out the data that you need to include in your Access Reports.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

There are a couple of ways. (And incidentally, "hybrid" was just an
adjective I used to describe it; I don't think you'll find that word in
Help for Access or Excel.)

I think the easiest is to (manually) set parameters such as amount due,
interest rate, etc., in Excel, maybe on the first worksheet of your
file, where they'll be easy to find. Have Excel do its calculations
based on those values, in an Excel table that you have perhaps put onto
another worksheet. Have Excel recalculate everything after you have
changed the parameters (maybe it will do this automatically).

Then you can link an Access Table to the Excel table and use it just as
if it were a Table in Access.

For example, I just now did that -- created a new Excel file in which
the first worksheet contains these cells (in which I used Insert -->
Name --> Create to name them):

Interest Principal Payment
5.80% $10,000.00 $400.00

And the second worksheet, [Sheet2], contains cells with values like these:

A B C D
Period Principal InterestAmt PPlusI
0 $10,000.00 $48.33 $10,048.33
1 $9,648.33 $46.63 $9,694.97
2 $9,294.97 $44.93 $9,339.89

.... which are based on formulas like these:

A3: =A2+1
B3: =D2-Payment
C3: =B3*Interest/12
D3: =B3+C3

(I know, these worksheet names aren't very suggestive, I should have
chosen better ones. Sorry.)

Then, in Access, I created two Tables linked to these. The first, on
[Sheet1], I set up via File --> Get External Data --> Link Tables ... ,
and I called it [Parameters]. I specified that the first row contained
field names.

The second Access Table I linked from [Sheet2], again specifying that
the first row contained field names, and I called this Table
[PaymentSchedule]. But for this one, not wishing to accidentally change
the values, I also defined a Query, [Q_PaymentSchedule], defined via
this SQL:

SELECT PaymentSchedule.*
FROM PaymentSchedule
ORDER BY PaymentSchedule.Period;

and I set its Recordset Type property to "Snapshot", to force it to be a
read-only recordset (Access will beep at you if you try to change one of
the values).

Having done this, I was pleased to see that, if I changed a value in
[Parameters], such as the monthly payment amount, not only did that
change in Excel, but Excel also recalculated its values, and
[Q_PaymentSchedule] immediately (well, within a couple of seconds)
reflected the changed values, where they'd be available for any Forms,
Reports, other Queries, etc., that you might want to set up.

Note that the file you link to is likely to have to be runnning. You
can do this in a Macro via the following action:

RunApp: Excel db5J18a.xls

Run this Macro before trying to use the linked Tables.

Also, it's likely that your Excel options (Tools --> Options) will have
to specify automatic recalculation.

Of course, all of this stuff can be done under program control (in a
Module, using VBA language), but I'm kind of assuming you didn't want to
get involved in anything like that.

Have fun.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks very much for that. There are several loans to keep a track of. How
would I go about the Hybrid option. Excel is working but I am not sure how
I would run queries from access with excel information. Could you advise? I
would greatly appreciate any help you could offer. Thanks again.

Carmen

:
[...]

You might also consider a hybrid approach. Although Access can do the
calculations, it's probably easier to leave them in Excel, and link an
Access Table to that table in Excel. After all, your Excel version is
already working, isn't it? You might define some Queries in Access to
pull out the data that you need to include in your Access Reports.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[PC Datasheet]

$55 is positive
-$55 is negative

CurrentBalance = DSum("TransactionAmount]","NameOfTransactionTable")

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.

 

[Carmen]

Thanks, I think I know what you mean. I am going to try it now. Thanks
again for your help

$55 is positive
-$55 is negative

CurrentBalance = DSum("TransactionAmount]","NameOfTransactionTable")

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.

Thanks for your help. I am a little confused still though. How does the
TransactionAmount get recorded as positive or negative.

For an example, would it be something like
If TransactionType = Debit then
TransactionAmount = Negative
else if TransactionType = Credit then
TransactionAmount = Positive

CurrentBalance query, how do I sum the TransactionAmount of all previous
TransactionDates.

Sorry to be a pain.

Carmen

 

[Carmen]

Thanks Vincent, I was not wanting to do much VBA coding as I am in the
process of learning. I will try your suggestions. Thanks again.

There are a couple of ways. (And incidentally, "hybrid" was just an
adjective I used to describe it; I don't think you'll find that word in
Help for Access or Excel.)

I think the easiest is to (manually) set parameters such as amount due,
interest rate, etc., in Excel, maybe on the first worksheet of your
file, where they'll be easy to find. Have Excel do its calculations
based on those values, in an Excel table that you have perhaps put onto
another worksheet. Have Excel recalculate everything after you have
changed the parameters (maybe it will do this automatically).

Then you can link an Access Table to the Excel table and use it just as
if it were a Table in Access.

For example, I just now did that -- created a new Excel file in which
the first worksheet contains these cells (in which I used Insert -->
Name --> Create to name them):

Interest Principal Payment
5.80% $10,000.00 $400.00

And the second worksheet, [Sheet2], contains cells with values like these:

A B C D
Period Principal InterestAmt PPlusI
0 $10,000.00 $48.33 $10,048.33
1 $9,648.33 $46.63 $9,694.97
2 $9,294.97 $44.93 $9,339.89

.... which are based on formulas like these:

A3: =A2+1
B3: =D2-Payment
C3: =B3*Interest/12
D3: =B3+C3

(I know, these worksheet names aren't very suggestive, I should have
chosen better ones. Sorry.)

Then, in Access, I created two Tables linked to these. The first, on
[Sheet1], I set up via File --> Get External Data --> Link Tables ... ,
and I called it [Parameters]. I specified that the first row contained
field names.

The second Access Table I linked from [Sheet2], again specifying that
the first row contained field names, and I called this Table
[PaymentSchedule]. But for this one, not wishing to accidentally change
the values, I also defined a Query, [Q_PaymentSchedule], defined via
this SQL:

SELECT PaymentSchedule.*
FROM PaymentSchedule
ORDER BY PaymentSchedule.Period;

and I set its Recordset Type property to "Snapshot", to force it to be a
read-only recordset (Access will beep at you if you try to change one of
the values).

Having done this, I was pleased to see that, if I changed a value in
[Parameters], such as the monthly payment amount, not only did that
change in Excel, but Excel also recalculated its values, and
[Q_PaymentSchedule] immediately (well, within a couple of seconds)
reflected the changed values, where they'd be available for any Forms,
Reports, other Queries, etc., that you might want to set up.

Note that the file you link to is likely to have to be runnning. You
can do this in a Macro via the following action:

RunApp: Excel db5J18a.xls

Run this Macro before trying to use the linked Tables.

Also, it's likely that your Excel options (Tools --> Options) will have
to specify automatic recalculation.

Of course, all of this stuff can be done under program control (in a
Module, using VBA language), but I'm kind of assuming you didn't want to
get involved in anything like that.

Have fun.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks very much for that. There are several loans to keep a track of. How
would I go about the Hybrid option. Excel is working but I am not sure how
I would run queries from access with excel information. Could you advise? I
would greatly appreciate any help you could offer. Thanks again.

Carmen

:
[...]

You might also consider a hybrid approach. Although Access can do the
calculations, it's probably easier to leave them in Excel, and link an
Access Table to that table in Excel. After all, your Excel version is
already working, isn't it? You might define some Queries in Access to
pull out the data that you need to include in your Access Reports.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Thanks Vincent, I was not wanting to do much VBA coding as I am in the
process of learning. I will try your suggestions. Thanks again.

Not (yet) being proficient in using VBA is not the only reason to use
some other technique; I like to use VBA when it's called for, but it's
not always needed.

Even though Access can do some arithmetic (even without VBA) by
specifying calculations in Queries and elsewhere, Excel is very good at
arithmetic, and as long as you have a copy that you can run at the same
time as you're using Access, why not take advantage of that? (Excel can
do some impressive things, like solving systems of equations, that are
kind of foreign to Access.)

Anyway, I'd be interested in knowing if my suggestion actually works for
you. Good luck with it.

:
[...]

Of course, all of this stuff can be done under program control (in a
Module, using VBA language), but I'm kind of assuming you didn't want to
get involved in anything like that.

Have fun.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.