Summing on one-to-many tables

[bigalligator]

I am using Crystal XI on Cache DB.
I am trying to make finance reports that sum the original cost of service, as well as the payments.

My problem is that I am linking:

service_table s (where the original record is)

left join charge_table c
(where the claims are made, and MAY have charges to multiple insurances, or none)
on s.client = c.client
and s.service_ID = c.service_ID
and s.facility = c.facility (which is the same number for every record in every table)

left join payment_table p (where there MAY be multiple or NO payments)
on c.client = p.client
and c.service_ID = p.service_ID
and c.insurance_ID = p.insurance_ID
and c.facility = p.facility

I am trying to sum the original service info, but it is duplicated because of the possibility of multiple charges or payments. How would I go about getting the correct information? Any help would be appreciated. I have tried different linking combos, running totals, groups.

I read through

http://kenhamady.com/cru/archives/52

(read this on a prior one-to-many post), and feel like this may be a good solution, but I do not know how to implement it through a sql command.

Also, I am not currently using a sql command, I just thought it would be easier to read if I wrote it that way.

 

[lbass]

You should be able to use running totals where you use the evaluate on change of option and/or the reset on change of option to eliminate duplicate values from the summary. It is hard to make a recommendation without seeing how the data is repeating in the detail section.

-LB

 

[bigalligator]

I thought there would be some type of linking solution, but running totals worked.