Code for Access Help

[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

 

[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.

 

[Marshall Barton]

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.

So, you want to get a result with contract length record
from a single record, right? If so you should use a query
with a helper table.

The helper table *named Numbers) would have a single field
(named Num) that is populated with the values 1, 2, 3, ...
up to more than the longest possible contract length.

Then the query could look something like:

SELECT Account#, [Total Fee], [Contract length],
IIf(Num = 1, [Total Fee] * FirstMonthPercentage,
[Total Fee] * (1 - FirstMonthPercentage) _
/ ([Contract length] - 1)
FROM [your table], Numbers
WHERE Num <= [Contract length]