Spliting the results of a query into other totals?

[ChrisSedgwick]

Hello,

I've built a query 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

The report is 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've included the code I've used to perform the first calculation. I was hoping for someone to be able to offer some support on finishing the end results, as described above. I'm not massively experienced with VBA, so explained as simple as possible would be a great help.

I've also attached the query results and a snapshot of the finished report that shows the total. What you'll notice is that the Projects that appear in one month and have Durations longer than one month, do not appear again on the report, nor do they contribute to the other figures which is what we're trying to achieve.

Hope you can help.

Thanks in advance.

Chris.

Forecast Contribution: IIf([Anticipated Duration of Our Workds] And [Project]![Probability] And [Quotes]![ContributionValue]>0,[Quotes]![ContributionValue]\[Project]![Anticipated Duration of Our Workds])*[Project]![Probability]

 

[JHB]

The only way I could see it done, (because it can't be done by a query, you can't fetch data out of the blue sky):

• Creating a table for holding the amount for each month
• Use a recordset which has the input from you first query.
• Use a loop and a recordset for inserting the value for each month in the newly created table.

If you need some more help then post your database with some sample data, zip it.