Preventing duplicate entries in totals

[JimmyEdwards]

I am using Crystal Reports version 4.6 (I know.) and connecting to an SQL database via ODBC.

I am looking to group accounts into the month they where loaded and total the number and balance. I have an outer left join to a payments table so that if an account has received multiple payments in the month the report is showing an entry for each payment, but still shows an account if it has had no payments.. The problem is that it also shows the balance of the account for every payment. Can I get the report to give me a total of the balances, only counting 1 instance of each balance? I do need the report to show every payment.

Any help would be gratefully appreciated.

 

[synapsevampire]

Sure, since the balance is always the same, you can create a Group inside the MONTHS (or vice versa) and just use MAXIMUM({table.balance},{table.date}) for display purposes, or just display the balance at the balance group headeer or footer.

If you provide example dtaa and expected output I can be more specific, but you probably get the idea.

-k

 

[lbass]

Is balance a table field or is it a calculated field (a formula)? If it is a formula, please provide the formula. It would help to see some sample data, too.

-LB

 

[JimmyEdwards]

Hi,

Here is the sample data. This is what I'm getting:-

payments received on accounts loaded in month 07 of 2002.

(Section - Page header)
Year [red]New Cases No.[/red] [blue]New Cases Balance[/blue] Payment
(Section - Group 1 Year header)
2002
(Section - Group 2 Month header)
07 [red]2[/red] [blue]500.00[/blue] 20.00
(Section - Details (hidden))
[red]73756[/red] [blue]100.00[/blue]
[red]73758[/red] [blue]200.00[/blue] 10.00
[red]73758[/red] [blue]200.00[/blue] 10.00

This is what I want:-

07 [red]2[/red] [blue]300.00[/blue] 20.00

[red]73756[/red] [blue]100.00[/blue]
[red]73758[/red] [blue]200.00[/blue] 10.00
[red]73758[/red] [blue]200.00[/blue] 10.00

In the New Cases No. column I have used a distinctcount fo the reference number (the 5 digit unique reference shown in the details section). I have done a maximum of the balance for each reference number, but then I can't subtotal that again to stick it in the month group header. All of the data is from table fields. Hope this is useful to you. Thanks for your help.

 

[JimmyEdwards]

Hi all,

I have now solved this problem using a running total. Firstly I've added a group for the reference number. The running total resets at the start of each month (in the month group header), adds the Max value for the balance for each unique reference, and then prints the total in the month group footer.

Cheers for all your help!

James