Summing two similar rows

[allivant]

I have five columns of data like this:

Col A: State
Col B: County
Col C: City
Col D: Month
Col E: Hours

If A, B, and C all match, I want to sum Col E. So it doesn't matter what D is. I probably won't even show D in the report. I just want one line instead of two or more.

Ex:

Virginia Fairfax Herndon Feb 5
Virginia Fairfax Herndon May 10

Should show:

Virginia Fairfax Herndon 15

I tried grouping but that didn't work. Can anyone help?

 

[lbass]

You should group on state, then county, then city. Place the groupnames in the city group header and suppress GH1 and GH2. Then place the hours field in the detail section and insert a sum at the city group level. Then suppress the details section and the group footers.

-LB

 

[allivant]

Thanks LB, that worked great. Now the only problem I see is sorting by the Hours. We'd like to be able to sort so the Cities with the highest hours are first so a descending list. I can't seem to do this in the Record Sort like I'm used to. I can't get Hours before the State, County, and City groups.

 

[lbass]

Go to report->group sort and select all->select "sum of {table.hours}" as your group sort field, in descending order.

-LB

 

[allivant]

Actually, now that I look at it it's not correct. I've attached two screen shots of before and after.

 

[lbass]

Your screen shots don't clarify the problem. However, because of the group sort, in retrospect I think you should create a formula to group on:

{table.state}+" "+{table.county}+" "+{table.city}

Then insert the sum at the group level and then do the group sort. You can use the actual fields to display in the body of the report instead of the group name, if you wish.

-LB