subtotals in reports

  • Thread starter Thread starter HSalim
  • Start date Start date
H

HSalim

Hello all,
I have a report that has multiple groups. Detail, YearFooter, ProjectFooter
and Department Footer

In the ProjectFooter, I have a text box where I am using a function to
calculate available workdays between 2 dates (Function taken from
http://www.mvps.org/access/datetime/date0006.htm )
Control Source = WorkingDays([ProjStartDate], [ProjEndDate])
Name = txtAvailWorkDays
This Does return available workdays for the project

However, I am not able to get a sum of the available workdays in the
Department footer.
I have a textbox with the control source set to = Sum([txtAvailWorkDays])

When I run the report, txtAvailWorkDays appears as a parameter, and i am
puzzled..
Is it not possible to sum a value from an intermediate footer? If not this
way, is there any way?

Any help would be most welcome. thanks in advance.

Regards
Habib
 
HSalim said:
I have a report that has multiple groups. Detail, YearFooter, ProjectFooter
and Department Footer

In the ProjectFooter, I have a text box where I am using a function to
calculate available workdays between 2 dates (Function taken from
http://www.mvps.org/access/datetime/date0006.htm )
Control Source = WorkingDays([ProjStartDate], [ProjEndDate])
Name = txtAvailWorkDays
This Does return available workdays for the project

However, I am not able to get a sum of the available workdays in the
Department footer.
I have a textbox with the control source set to = Sum([txtAvailWorkDays])

When I run the report, txtAvailWorkDays appears as a parameter, and i am
puzzled..
Is it not possible to sum a value from an intermediate footer? If not this
way, is there any way?
Click to expand...


The aggregate functions only operate on fields in the
form/report's record source table/query, they are unaware of
controls.

You can use:
=Sum(WorkingDays([ProjStartDate], [ProjEndDate]))

Or, you can use a text box with its RunningSum property set
to Over Group to get what you want.
 
Marsh,
Thanks for replying. I tried using running sums but was having trouble with
getting it to work over multiple groups.
Eventually, I just calculated Working Days right in the query and it works
well enough, for now at least.
I suspect performance will suffer significantly as the database grows.

Regards
HS



Marshall Barton said:
HSalim said:
I have a report that has multiple groups. Detail, YearFooter, ProjectFooter
and Department Footer

In the ProjectFooter, I have a text box where I am using a function to
calculate available workdays between 2 dates (Function taken from
http://www.mvps.org/access/datetime/date0006.htm )
Control Source = WorkingDays([ProjStartDate], [ProjEndDate])
Name = txtAvailWorkDays
This Does return available workdays for the project

However, I am not able to get a sum of the available workdays in the
Department footer.
I have a textbox with the control source set to = Sum([txtAvailWorkDays])

When I run the report, txtAvailWorkDays appears as a parameter, and i am
puzzled..
Is it not possible to sum a value from an intermediate footer? If not this
way, is there any way?
Click to expand...


The aggregate functions only operate on fields in the
form/report's record source table/query, they are unaware of
controls.

You can use:
=Sum(WorkingDays([ProjStartDate], [ProjEndDate]))

Or, you can use a text box with its RunningSum property set
to Over Group to get what you want.
Click to expand...
 
Yeah, running sums across groups can be a little tricky, but
it can be done ;-)

I should have mentioned calculating the workdays in the
query, which is a perfectly valid way to do it as long as
you're using a Jet database. I haven't tested it, but I
suspect doing it in the query is about the same as using the
aggregate funvtion in the report (maybe even a little
faster), but don't quote me on that ;-)
--
Marsh
MVP [MS Access]


I tried using running sums but was having trouble with
getting it to work over multiple groups.
Eventually, I just calculated Working Days right in the query and it works
well enough, for now at least.
I suspect performance will suffer significantly as the database grows.

I have a report that has multiple groups. Detail, YearFooter, ProjectFooter
and Department Footer

In the ProjectFooter, I have a text box where I am using a function to
calculate available workdays between 2 dates (Function taken from
http://www.mvps.org/access/datetime/date0006.htm )
Control Source = WorkingDays([ProjStartDate], [ProjEndDate])
Name = txtAvailWorkDays
This Does return available workdays for the project

However, I am not able to get a sum of the available workdays in the
Department footer.
I have a textbox with the control source set to = Sum([txtAvailWorkDays])

When I run the report, txtAvailWorkDays appears as a parameter, and i am
puzzled..
Is it not possible to sum a value from an intermediate footer? If not this
way, is there any way?
Click to expand...

"Marshall Barton" wrote
The aggregate functions only operate on fields in the
form/report's record source table/query, they are unaware of
controls.

You can use:
=Sum(WorkingDays([ProjStartDate], [ProjEndDate]))

Or, you can use a text box with its RunningSum property set
to Over Group to get what you want.
Click to expand...
Click to expand...
 
Back
Top