Summary Fields not totalling correctly

[MarkDraa]

I have a billing database joined to my main client database using an alphanumeric client-code field. Each month, billing amounts for each client are imported into the billing database. None of the four join options are checked for this join.

I have a report in my client database that shows a line for each client and the billing amount for each month. At the bottom is a grand summary containing calculated fields [SSum(BillDB.JanuaryBill) for example].

When I print this report for all clients (about 600), the value of the summary field is greater than the actual sum of all JanuaryBill fields. In other words, the total January bill amounts imported is $50,324. When I look at this field from a report in the billing database, the total shows correctly. When I print the report that is in the client database, the summary field shows $66,123.

I've tried finding a small set of say, 25 clients, and printing the report. The totals in two different finds were indeed correct. When I do a find of a much larger size, say 250 clients, then the totals are once-again too high. I've actually manually added the column of numbers that prints out to prove this, by the way.

Since I've checked the verified that the actual data in the billing database is correct, it must be something about my summary or join that is causing the total to loop or something. Since the report contains fields from both the client and billing databases, I've tried switching the "main database" for the report between the two, but that doesn't change the totals. The main database is currently set as the client database.

How do I troubleshoot this one?

TIA!

Mark

 

[suesloan]

To SSum the billing db fields correctly, you need to base the report on the billing db. A leading and trailing set of summary panels to display customer info and SSum the billing records would group by the customer "client-code" field or by name...

If you do not have any other summary panels in that report other than the grand summary at the bottom, the SSums will be correct. Make sure the summary options in Field Definition for these SSum fields are "Summary Panels where placed".

Sue Sloan

 

[MarkDraa]

Thought I'd post the final solution - After setting the main database for the report to the billing database the totals were still too high, so I spent some time pawing though the data in the billing database. It turns out that there were 4 copies of a record from a botched import a few months ago. I deleted the duplicates and everything now matches.

I should have suspected the simplest problem first, in retrospect. Occam's razor & all.

Thanks!

Mark