CR 8.5, one charge to several payments 1

[azoe]

My SQL query returned the same charge on every record where a payment was applied to it.

so I have these columns:
ServiceVisitID, charge, payment, serviceCode

And these records:
10031, 150.00, 50.00, 122
10031, 150.00, 25.00, 122
10022, 200.00, 75.00, 123

On visit 10031 I want the total of charges to show 150.00 not 300.00.

I'm not sure if this is something I can do with a formula in Crystal or if it is something I have to deal with in my SQL query.

Any help is appreciated.
Thank you

 

[lbass]

This is the natural result of joining two tables where there is a one to many relationship. You can use the running total expert to create a running total for the charge column where you choose {table.charge}, sum, evaluate on change of field->{table.servicevisitID}->reset never (if this is a grand total you are looking for) or on change of group (if you want a group subtotal). You should be able to insert a summary on the payment field--unless you also have many charges for a given account, i.e., a many to many relationship, in which case you'd want to use a running total for that also. Note that running totals must be placed in footers to evaluate correctly.

Otherwise, you could also use a SQL expression for the payment summary. See thread149-1010208 for details on that approach.

-LB

 

[azoe]

I haven't used Crystal since before it could do running totals but I saw in a FAQ that you could go to Insert, Running Total Wizard - but I'm not seeing that in my Insert menu. I'll keep looking - thanks for your help.

 

[lbass]

In 8.0, after applying the service pack, the running total expert appears in the field explorer. Go to Insert->Field Object->Running Total. Or you should be able to right click on a field->insert->running total.

-LB