Sum Question

[metalteck]

I currently have a report that shows account activity.
In this report, the primary fields are being pulled from one table. There is one field called lpchgs, and this is the total charge for an account.

Ex. Account Total Charges
444444 $152.00
I created a report to show an accounts activity, but when I summarize and grand total it out, the total of the grand total charges is 4 times as much. I figured out that its calculating for each line item, instead of the one instance. I want it to only calculate once, can you help me.
I'm using Crystal 8.5

Ex. Account Total Charges
444444 $152.00
444444 $152.00
444444 $152.00
444444 $152.00
Grand Total is $608.00 Acutal is $152.00

Here is my code:
SELECT
BADPLPP."LPACCT", BADPLPP."LPMRC#", BADPLPP."LPPLNM", BADPLPP."LPPFNM",
BADPLPP."LPFC", BADPLPP."LPADT1", BADPLPP."LPADT2", BADPLPP."LPADT3",
BADPLPP."LPLDD", BADPLPP."LPPTYP", BADPLPP."LPCBAL", BADPLPP."LPCHGS",
BADPLPP."LPADJ", BADPLPP."LPPMTS", BADPLPP."LPHSP#",
BADPHBP."HBINS#", BADPHBP."HBPLAN",
BPBSSAP."SACSTS"
FROM
("AS400"."BSYDTAA"."BADPLPP" BADPLPP INNER JOIN
"AS400"."BSYDTAA"."BPBSSAP" BPBSSAP ON
BADPLPP."LPACCT" = BPBSSAP."SAACCT" AND
BADPLPP."LPHSP#" = BPBSSAP."SAHSP#")
INNER JOIN "AS400"."BSYDTAA"."BADPHBP" BADPHBP ON
BADPLPP."LPACCT" = BADPHBP."HBACCT"
WHERE
BADPLPP."LPHSP#" = 1 AND
BPBSSAP."SACSTS" = 'a' AND
BADPLPP."LPCBAL" >= 10000
ORDER BY
BADPLPP."LPACCT" ASC

 

[lbass]

You could first try going to database->select distinct records. If that doesn't solve the issue, then first group on account and then insert a running total that sums the charges field, evaluate on change of group: account, and resets never. Place the result in the report footer.

-LB

 

[metalteck]

Thank you, this was a big help.