Splitting the totals into consecutive months?

[ChrisSedgwick_]

I've built a query in Access to perform a simple calculation. To take a Contribution figure, which effectively is our Sales Figure, the Anticipated Start Date of the Project and then divide the Contribution by the Duration of the Project. For example


The Contribution of a Project is say, £10,000
The Anticipated Start Date is, 1st Jan
The Duration of Project will be, 5 months
The result of the query is £2,000


I then have a function that allows me to Export into Excel to then manipulate the data.

The results are then grouped by the Anticipated Start Date to give a forecasted Contribution figure for that month, so January would be £2,000.


Everything that I've done so far works perfectly, just how we want it. Except for...


I need the remaining Contribution, in our case £8,000 to be split in to the remaining months, according to the Duration of the Project. Again, for example.


Total Contribution = £10,000
Ant. Start Date = 1st Jan
Duration of Project = 5 months
Query Result = £2,000


I then want the remaining £8,000 to be split over the remainder, so...


Jan = £2,000
Feb = £2,000
Mar = £2,000
Apr = £2,000
May = £2,000


This way we can total a figure for each month, which will give us an estimated Contribution figure for a specific period.


I was hoping for someone to be able to offer some support on finishing the end results and manipulating the data, as described above in Excel. I'm not massively experienced with VBA, so explained as simple as possible would be a great help.


Hope you can help.


Thanks in advance.


Chris.