Conditional Sum in Report

  • Thread starter Thread starter Judy Freed
  • Start date Start date
J

Judy Freed

Hi
I have the following scenario, using Access XP. I have a report that is
grouped by "CategoryA". Under each category heading is a record that
identifies "User", "Activity", "Date" and "Hours". My group footer sums the
hours for each "Category A". and the report is sorted by date within each
group.

What I would like to be able to do is: On the report footer, I would like
to list each activity and the Total number of hours for it. I am at a loss
as to how to make it happen - DSum does not work. Can anyone help?

Thanks

Judy Freed
Systesm Development
UNC Charlotte
 
Hello Judy,

You could do one of the following:

1. Add a SubReport that is grouped by the Activity field.
Here you could add the summ of each Activity.

OR

2. If you have a fixed number of Activities, lets say 3,
Activity_A Activity_B and Activity_C, then you could add 3
conditional controls to the section of the report that
displays the Activity field.

Change controls properties as shown:

txtActivityASum:
ControlSource = iif([Activity]="Activity_A",[HOURS],0)
Visible = No
RunningSum = OverAll

txtActivityBSum:
ControlSource = iif([Activity]="Activity_B",[HOURS],0)
Visible = No
RunningSum = OverAll

txtActivityCSum:
ControlSource = iif([Activity]="Activity_C",[HOURS],0)
Visible = No
RunningSum = OverAll

In the Report Footer add the following controls:

txtActivityATotal = txtActivityASum
txtActivityBTotal = txtActivityBSum
txtActivityCTotal = txtActivityCSum


Hope this helped.

ET Sherman
 
Hi

I opted for your second suggestion, with one variance - I decided to put the
Sums in the group footer for display purposes. Everything worked
wonderfully. Thank you so much for taking the time to assist.

ET Sherman said:
Hello Judy,

You could do one of the following:

1. Add a SubReport that is grouped by the Activity field.
Here you could add the summ of each Activity.

OR

2. If you have a fixed number of Activities, lets say 3,
Activity_A Activity_B and Activity_C, then you could add 3
conditional controls to the section of the report that
displays the Activity field.

Change controls properties as shown:

txtActivityASum:
ControlSource = iif([Activity]="Activity_A",[HOURS],0)
Visible = No
RunningSum = OverAll

txtActivityBSum:
ControlSource = iif([Activity]="Activity_B",[HOURS],0)
Visible = No
RunningSum = OverAll

txtActivityCSum:
ControlSource = iif([Activity]="Activity_C",[HOURS],0)
Visible = No
RunningSum = OverAll

In the Report Footer add the following controls:

txtActivityATotal = txtActivityASum
txtActivityBTotal = txtActivityBSum
txtActivityCTotal = txtActivityCSum


Hope this helped.

ET Sherman


-----Original Message-----

Hi
I have the following scenario, using Access XP. I have a report that is
grouped by "CategoryA". Under each category heading is a record that
identifies "User", "Activity", "Date" and "Hours". My group footer sums the
hours for each "Category A". and the report is sorted by date within each
group.

What I would like to be able to do is: On the report footer, I would like
to list each activity and the Total number of hours for it. I am at a loss
as to how to make it happen - DSum does not work. Can anyone help?

Thanks

Judy Freed
Systesm Development
UNC Charlotte


.
Click to expand...
Click to expand...
 
Back
Top