Unwanted multiples in sum fields in group footers

[Evan Robitaille]

I have a report that has information displayed in the the following groups:

Name Field : Has Header & Footer
Account : Has Header & Footer, fields include [Balance]. One record only.
Details : Has multiple rows for different collateral.

When I try to sum the [Balance] field, it takes the balance and multiples it by the number of pieces of collateral displayed. For Example, if the Balance is $100 in the account header and there are three pieces of collateral, the sum on the account footer is $300.

Thanks in advance for any help with this, you guys are greatest.

 

[boblarson]

It sounds to me like you might not have your query linked properly. Do you have a query underlying the report that is based on more than one table? Or, you aren't actually summing the correct field. How are you getting the header?

 

[Evan Robitaille]

Using two tables. Table one has account information with one recordset for each account. The Account Number field links (outer join) to a collateral table that has multiple recordsets for most account numbers. The linking field is account number and then a collateral id field in the second table differentiates the records in that table. In my report I have an Account Number header with info including a currency field, then the collateral is listed in a detail section. If I use a =sum in the Account Number footer section the result is the amount of the currency field in the Account Number header times the number of lines shown in the detail section. I hope this explains it better. Thanks again for your help.

 

[boblarson]

It sounds like you do have a link missing. Is there any way to attach your database here? Or, can you open the query underlying the report and take a screenshot so I can see your links?

 

[Evan Robitaille]

I have uploaded a zip of the database with very basic tables and report. You will see on the report that the sum in the section footer increases by the number of lines in the detail section. Thanks again for your help.

 

[boblarson]

It was pretty much as I thought, but the stuff that you wanted was to show many details for each record. So, I changed it to a report/subreport. Check it out to see if this will work for you.


Oh, also - I renamed your query and report to remove the "/" sign. You do not want to use special characters in field names or object names. You also do not want to use reserved words as field names or object names (things like Name, Date, etc.). It will also make life a whole lot easier on you if you don't use spaces in field names or objects (although not required).