Totals in Reports (Again)

[wizard_chef]

I seem to have an ongoing problem with totals in reports. Here is the
latest mystery.

I have a customer-sales relational database with linked tables:
customer, order, order details, payment, etc. I am trying to create a
simple report that will show the sales recorded as reported according
to a specified date of bank deposit. I run a query that is accessed by
my report, and I gather: customer name, the items purchased by purchase
date, the quantity and per unit price, the shipping, and the payment
made by the customer (not always equal to the order total).

In the report under a "paymentdatefooter" I show the "this order total
including shipping" (=sum([quantity*unitprice]) + freightcharge). There
is no grouping set on this quantity. I also show the "paymentamount"
that was paid for this order. No grouping here. Everything here
computes OK for each order in the report. Now, in the Report Footer I
computer the bank deposit (the total for this report) grand total which
is simply "=sum([paymentamount])". (Again, of course, no grouping.)

For some strange reason, the report grand total is adding one of the
orders twice. This particular order has a shipping charge associated
with it (none of the others have this), making me think the problem has
something to do with the shipping charge.

I am stumped. It is too straightforward.

Ideas, anyone?

 

[wizard_chef]

OK, the problem arises in the query. For each item in an order, the
query shows the payment for the total order. So, when the report totals
"paymentamount" is sees two payments for the order in question.

How do I make the report only "see" a single payment for each order, as
it seems to show in the report paymentdatefooter?? I tried assigning a
name to the paymentamount in the paymentdatefooter and using that name
in the reportfooter grand total, but it won't recognize the name.

Anyone any thoughts on this?

wizard_chef

 

[Duane Hookom]

Apparently you payment amounts are not "tied" to order details. You can add
a text box to the Order group header or footer and set its Running Sum
property to Over All. Then add a text box to your report footer with a
control source of:
=[Name Of Running Sum Text Box]

--
Duane Hookom
MS Access MVP
--

OK, the problem arises in the query. For each item in an order, the
query shows the payment for the total order. So, when the report totals
"paymentamount" is sees two payments for the order in question.

How do I make the report only "see" a single payment for each order, as
it seems to show in the report paymentdatefooter?? I tried assigning a
name to the paymentamount in the paymentdatefooter and using that name
in the reportfooter grand total, but it won't recognize the name.

Anyone any thoughts on this?

wizard_chef

I seem to have an ongoing problem with totals in reports. Here is the
latest mystery.

I have a customer-sales relational database with linked tables:
customer, order, order details, payment, etc. I am trying to create a
simple report that will show the sales recorded as reported according
to a specified date of bank deposit. I run a query that is accessed by
my report, and I gather: customer name, the items purchased by purchase
date, the quantity and per unit price, the shipping, and the payment
made by the customer (not always equal to the order total).

In the report under a "paymentdatefooter" I show the "this order total
including shipping" (=sum([quantity*unitprice]) + freightcharge). There
is no grouping set on this quantity. I also show the "paymentamount"
that was paid for this order. No grouping here. Everything here
computes OK for each order in the report. Now, in the Report Footer I
computer the bank deposit (the total for this report) grand total which
is simply "=sum([paymentamount])". (Again, of course, no grouping.)

For some strange reason, the report grand total is adding one of the
orders twice. This particular order has a shipping charge associated
with it (none of the others have this), making me think the problem has
something to do with the shipping charge.

I am stumped. It is too straightforward.

Ideas, anyone?

 

[wizard_chef]

That fixed it. Why does using a running sum on a named variable "tie"
it to the report footer? Why can I find more on this? I want to
understand it, as it comes up for me all the time. None of my books,
none of my Google searches on this site, nor Access help yield any
light on this subject. Can you point me to a source?

thanks.

wizard_chef

 

[Duane Hookom]

I have seen this basic question answered many times in the Reports news
group. The Running Sum functionality has several uses. Do a search on
"Running Sum" in Google Groups.

There are other ways of getting the same totals. One method involves
creating the totals you need in a separate query and join it into your
report's record source query.