Code for Access Help


C

ChrisSAustin

Hello,

I need to create a report that seperates a total fee into one monthly charge
of X%, then equal monthly amounts depending on the contract length. ie a
$1000 charge with a 25% first payment and 11 month contract would show a
first month charge of $250 then 10 equal payments of $75.

My input is a table with the following column headings: Account#, Total Fee,
Contract length, FirstMonthPercentage.

I would like the output to be a table with the same columns as above, but
with each months payment following on the same row.

Although I am pretty good with Access, I do not know any VB and can't think
of a way to do this with a macro. I tried using a query that calculated 12
months but it became too complicated to run.

Thanks
 
Ad

Advertisements

B

Beetle

You shouldn't need code for this, a simple query should do the trick;

SELECT [AccountNo], [TotalFee], [ContractLength], [FirstMonthPercent],
[TotalFee]*[FirstMonthPercent] AS FirstMonthAmount, [ContractLength]-1 AS
MonthsRemaining, ([TotalFee]-[FirstMonthAmount])/[MonthsRemaining] AS
MonthlyPayment
FROM YourTable;

Something like the above query should work if you just need to generate
a simple report at the beginning of the contract period. Also, I have
assumed that your FirstMonthPercent field is a Single or Double data
type with values like .10, .25, etc. and that no fields will be Null or have
zero as the amount (otherwise you'll get a division by zero error in the
calculated MonthlyPayment field). If those assumptions are incorrect then
the query would have to be modified to account for that.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top