Conditional Sum in Report

[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

 

[ET Sherman]

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

 

[Judy Freed]

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.

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


.