Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Summing on one-to-many tables

Summing on one-to-many tables

Summing on one-to-many tables

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.

RE: Summing on one-to-many tables

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.


RE: Summing on one-to-many tables

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close