report totals

[rkg]

I have a report based on a query that works like this:
Customer
Commodity #1 Wt. Price Total
Commodity #2 Wt. Price Total
Total of
Above
Less Accounts Rec. xxxxx
Total Due
In my query I have a query that is linked by the customer name that
matches the name with their corresponding account receivable. The query
alone works fine and shows only one total for the customers accounts
recievable. When I link it to my main query it lists the account
receivable for every line that has the above values. Since most
customers have several different commodities the account receivable
amount is listed on each line for each commodity. On the customer page
it lists it only once and everything works fine. For my report footer
though when I add a text box with the control source Sum([acctsrec]) it
sums every line. So if customer one has five commodities it adds
duplicate accounts receivable for each different commodity. Can someone
tell me how to limit it to one accounts receivable for each customer.
Any help would be appreciated.
Thanks
Rong

 

[Marshall Barton]

I have a report based on a query that works like this:
Customer
Commodity #1 Wt. Price Total
Commodity #2 Wt. Price Total
Total of
Above
Less Accounts Rec. xxxxx
Total Due
In my query I have a query that is linked by the customer name that
matches the name with their corresponding account receivable. The query
alone works fine and shows only one total for the customers accounts
recievable. When I link it to my main query it lists the account
receivable for every line that has the above values. Since most
customers have several different commodities the account receivable
amount is listed on each line for each commodity. On the customer page
it lists it only once and everything works fine. For my report footer
though when I add a text box with the control source Sum([acctsrec]) it
sums every line. So if customer one has five commodities it adds
duplicate accounts receivable for each different commodity. Can someone
tell me how to limit it to one accounts receivable for each customer.

Instead of using Sum to total the amounts in the report
footer, add a text box named txtRunDue to the Customer group
footer section. Set its control source to the same thing
you have in the Total Due text box and set its RunningSum
property to Over All. Then the report fotter text box can
display the grand total by using the expression =txtRunDue

 

[ronmalt]

That worked great. One more question, is there any way to do the same
thing and run a sum on only positive or negative numbers?

thanks,
RonG

 

[Marshall Barton]

That worked great. One more question, is there any way to do the same
thing and run a sum on only positive or negative numbers?

Use another text box in the Customer group
footer section. Set its control source to the expression:

=IIf(txtTotalDue < 0, txtTotalDue, 0)

and use the same kind of running sum arrangememt as before.

 

[rkg]

Thanks again. You've saved me a lot of time.

Ron G