Calculating monthly charges

[Sean Bishop]

I am using Access 2000

I have an lump sum which I have to charge out each month. In individual
records this amount can change during the year which means the amount
recharged can change for the rest of the year. For estimating purposes I also
need to know the projected recharges for the rest of the year. For this
reason the monthly recharges are posted to a table.

I have a query which does the calculations which runs along the lines Month1
= lump/12 ; Month2 = (lump-Month1)/11 ; Month3 = (lump-(Month1+Month2))/10
etc etc.

When run Month2 does not know the answer to Month1 so it divides the lump by
11 and this repeats across the query.

I can get round this by creating a macro that runs the query 12 times

Additionally once the month has been recharged the figures are locked in so
each month the query has to be be changed by deleting the calculation for the
the month recharged.

This is a convoluted way of getting the information but it works.

My Question:

Is a query the wrong approach to this ?

Would I do better to create a Visual Basic module ?


Thank you in advance for your help and advice.

Sean Bishop

 

[Ken Snell \(MVP\)]

Very difficult to give a firm answer without knowing a lot more about your
data, how you're using the "lump" charge, etc. A query may be the better /
easier way to go, or using a VBA function may be better / easier. If you can
tell us more details about your database, we can provide a more definitive
answer.