Help with Summary Totals at the end of the report 1

[Kirzman]

Ok bear with me I'm not great with Access! Basically I have a report which has the following headings

Project, Area, Cap/Rev, Actual, Budget, Forecast

The report is sorted via Area. (North, Central and South). I then have the report subtotalling using footers to bring through North Cap, North Rev, South Cap, South Rev etc totals.

However at the very end of the report I was a total line for Capital Projects and a total line for Revenue Projects (regardless of the Area it's in)

How on earth do I do this!! Any help much appreciated. Apologies if this is not explained well. I've tried the IIF command but can't get it to work.

 

[dhookom]

You should be able to use the same exact control sources in your report footer as your group footers. How are you calculating North Cap vs North Rev?

What have you tried and what results did you get? I'm not sure why you would need IIf().

Duane
Hook'D on Access
MS Access MVP

 

[Kirzman]

Ok the the report is sorted by Area and then Cap/Rev

So I have North Area Cap projects grouped together
Then North Area Rev Projects grouped together etc

In the footers of "CAP/REV" and "Area" I have inserted a sum command so this then gives me a total for Cap and Rev and then a total each time the Area changes. I'm happy with this

I just then want a grand total at the end of all Cap projects and all Rev projects

I've tried the IIF command so where it says CAP it will then sum up actual to date. But obviously I'm no expert and all I get is the total sum of CAP and REV or a false statement. So obviously it aint working! I'm not even sure IIF is the right command to use. Probably not. It's like I need a WHERE command. Where CAP/REV field says CAP then Sum...??

Can I not somehow do Sum of North Area + Sum South Area etc

Thanks for your help so far.

 

[dhookom]

You haven't provided any sample data or good description of what is contained in your fields. Can we assume the CAP/REV field contains either "CAP" or "REV"? Also, you mention " Capital Projects and ... Revenue Projects" without specfically linking these to CAP or REV values in the CAP/REV field. You must be more descriptive and possible provide sample data and desired calculations.

However, if you want to sum the Actual field for all records in a report that have a [CAP/REV] value of "CAP", the control source would be:

  =Sum(Abs([CAP/REV] = "CAP") * [Actual])

Duane
Hook'D on Access
MS Access MVP

 

[Kirzman]

Apologies.... Yes the Cap / Rev field contains only Cap or Rev. I'm not quite sure I understand your next point but never the less the code you gave me worked and thanks very much. I've combined it with an AND command on my other report and it's all working great

So thanks Again.