want to add calculations to database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am an Access novice. I can handle simple datbases and queries. I have
version 2002.

I have an existing database that post loan rates on a web site. I want to be
able to add calculations that will show what the payment will be for each
$1000 over the length of the term of the loan.

Is there a tutorial that will show me what to do? Any help or suggestions
will be appreciated.
Thanks,
pwog
 
I have an existing database that post loan rates on a web site. I want to be
able to add calculations that will show what the payment will be for each
$1000 over the length of the term of the loan.
Click to expand...

Create a Query based on your table. In a vacant Field cell you can
type a fieldname followed by a colon, and then the expression or
function call that does the calculation for you. Access doesn't have
loan-amortization functions builtin (as, for example, Excel does) so
you may need to write a function or link to the Excel library to do
this.

Simple example:

SumOfAAndB: [A] +

More realistic example:

Payment: MyPaymentFunction([Principal], [Term], [Rate])

where you've created a Module containing

Function MyPaymentFunction(Principal as Currency, Term as Integer,
Rate as Float) As Currency
<several lines of VBA code to do the calculation>
MyPaymentFunction = <the result of the calculation>
End Function

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Hey John,

Thanks for the reply. I don't suppose Access has a wizard to help with the
VBA? I guess I was hoping that access could handle functions like excel. Any
other suggestions? Thanks - P

John Vinson said:
I have an existing database that post loan rates on a web site. I want to be
able to add calculations that will show what the payment will be for each
$1000 over the length of the term of the loan.
Click to expand...

Create a Query based on your table. In a vacant Field cell you can
type a fieldname followed by a colon, and then the expression or
function call that does the calculation for you. Access doesn't have
loan-amortization functions builtin (as, for example, Excel does) so
you may need to write a function or link to the Excel library to do
this.

Simple example:

SumOfAAndB: [A] +

More realistic example:

Payment: MyPaymentFunction([Principal], [Term], [Rate])

where you've created a Module containing

Function MyPaymentFunction(Principal as Currency, Term as Integer,
Rate as Float) As Currency
<several lines of VBA code to do the calculation>
MyPaymentFunction = <the result of the calculation>
End Function

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Hey John,

Thanks for the reply. I don't suppose Access has a wizard to help with the
VBA? I guess I was hoping that access could handle functions like excel. Any
other suggestions? Thanks - P
Click to expand...

Well, not a wizard that knows how to build amortization queries I
don't think...!

Try a Google Groups advanced search for "amortization function" in the
microsoft.public.access.* newsgroups. I'm sure someone has done this
but can't put my finger on a specific cite.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top