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

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.

--
_________

Sean Bailey


"ChrisSAustin" wrote:

> 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