YTD totals on monthly report

[sly like Coyote]

I have a report I'm building that allows the user to check all allocations made for a specific program; the user can either do a full history of a program (all allocations that have been done), or a given time period. Part of this report is going to be showing the total budget, what was spent during the specified time period, how much has been spent (total), and the remaining balance.

I'm having trouble with the total spent and the remaining balance. The query the report is based on doesn't include this information, or more accurately the data needed to calculate it, once it's been restricted to the allocations made within date range and program ID the user specifies.

Do I need to have a second 'parallel' query that contains ALL the allocation data for all programs and then use a control bound to that recordsource and restricted to the proper program to do this, or is there a less convulated way that I'm missing?

 

[JamesMcS]

Depends where you're displaying the results really - could you just use a calculated control? If you're displaying total spent and remaining balance in two separate controls in the same place, could you not just do control1.value - control2.value as the control source for a third?

 

[sly like Coyote]

Depends where you're displaying the results really - could you just use a calculated control? If you're displaying total spent and remaining balance in two separate controls in the same place, could you not just do control1.value - control2.value as the control source for a third?

That's essentially what I'm doing, but control2.value isn't always the whole of the spending.

Let's say I have a program that's been running for 6 months, and the user specifies they want the report for the most recent full month. When they click the command to generate the report, it generates a 'where' clause that limits the report to the correct program and transactions (data from another form included in the report) that have happened between the specified dates.

When I run the calculated controls on the report itself, the only data it has to work with is based on this restricted query so the 'total spent' will not include any transactions from the rest of the program history. So you'd need control1.value (the program budget) control2.value (the sum of all the allocations in this time period) AND control3.value (the sum of all the allocations NOT in this time period, or I suppose just the sum of ALL allocations period) in order to calculate the remaining balance.

I need a way to populate control3 when the report's data source query has been restricted to a subset of the full data.

 

[sly like Coyote]

Nevermind, I figured out how to get to the data I needed. I don't need to worry if it's included in the restricted query results is I just use DSum to create a caculated field.