Same values in different fields

[janyc]

I have a table with entities and baseAmounts as the following example:

entity1 base1 entity2 base2 entity3 base3
1 TCO 100 CAT 200 ACT 300
2 CAT 400 ACT 500 TCO 600

I need to sum all like Entities:
Sum of all TCO
Sum of all CAT
Sum of all ACT

I have over 100 different entities in 10 fields.

Any suggestions?

 

[Madawc]

Normally I'd suggest running totals, but it sounds like there are 1000 different combinations, a lot of work. I assume you've thought of that, though it is best to say what you've already tried or considered when asking for help.

If you've got an SQL database and can code SQL, you could create an SQL Command (Stored Procedure) that would split the record details into ten separate records, after which it is easy to add them. Number them 1 to 10 and retain the original data, if you need to display it.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[TeyBrady]

I think there is an SQL solution as well. I think you could "union all" each of the entity groups to make it look like there is only one and then it becomes simple.

select entity1, base1 from yourtable
union all
select entity2, base2 from yourtable
union all
select entity3, base3 from yourtable

That causes the data to come back like this which is easier to work with.

entity1 base1
1 TCO 100
2 CAT 200
3 ACT 300
4 CAT 400
5 ACT 500
6 TCO 600

I was lazy and didn't rename the fields to something like entity and base...

If you want the sum of all the entities you can do something like this.

select entity1, sum(base1)
from (
select entity1, base1 from yourtable
union all
select entity2, base2 from yourtable
union all
select entity3, base3 from yourtable) x
group by entity1

It's summing all of the entities even though it says entity1 and base1 because of the way the union names the fields.

This should return
1 TCO 700
2 CAT 600
3 ACT 800