Unexpected running total changes...

[GIScrew]

I have running totals that generate weighted averages for 3 different fields in the report footer. The weighted averages come from a static table, table 1. Table 2 data changes every month or so. Table 2 has no influence over table 1 yet when I change the datasource for table 2, the weighted averages change anywhere from 00.01% to 2.00%. The weighted averages should not flucuate due to not changing the datasource for table 1. All of the tables are in the same database. Hope it all makes sense. Anyone have ideas?
Thanks,
Grant

 

[mbarron]

Are table 1 and 2 linked in any way?

As an example: Table one is linked to table two by customer number. If a customer has two transactions in table two it could cause table one's data to be doubled if there is an equal join between the two tables.

Mike

 

[GIScrew]

Yes, table 1 and table 2 are linked by a Left Outer Equal Join. Could the linking be the problem? What link type do you suggest?
Thanks,
Grant

 

[Madawc]

If table 1 could have zero entires in table 2, or several in table 2, then left-outer is right if you want to show all instances of table 1.

I'm surprised your wieghted average is having trouble. It doesn't include anything from table 2 in the weighting, does it?

Maybe do an experimental verion of the report in which you put the running total on each detail line and maybe get a clue as to what's going wrong.

It helps to give your Crystal version, since newer versions have extra solutions, and some extra problems. I use Crystal 8.5

Madawc Williams (East Anglia)

 

[mbarron]

Madawc,
Here's why the one-to-many relationship may be causing the problem. Unfortunatley it's because I have been bitten by it more than once. Borrowing from my example of two transactions for a customer in table2 for a period. If you are summarizing on a field from table1, its value will be calculated twice in the summary.

GI,
I'm not sure if changing the linking will help. You may have to resort to doing a manual running total.



Mike

 

[Madawc]

if it is the same table 1 record read twice, then couldn't it be fixed by a running total formula,

{Accno} <> Previous({Accno})

Madawc Williams (East Anglia)