sales journal report subtotals

[Anth Stever]

Here are the tables involved:

=======Orders=========
OrderID
SalesTax (amount, not rate)
ProductTotal
DeliveryCharge
AmountPaid

=====Order Details=======
OrderID
Product
Price
Qty
Discount
======================

I use a query to join the two and that is the basis for my report. In the
Detail section of the report, I have the fields from Order Details. The
fields from the Orders table are in the OrderID Footer section. So far, no
problem. In the Report Footer, I want to sum the fields from the Orders
table. That doesn't work because in the query each row contains the
DeliveryCharge for the entire order. Therefore, =sum(deliverycharge)
doesn't give me the total of all orders' delivery charges. Instead, it
gives me sum(DeliveryCharge * # of items on Order). How do I fix this?

At the end of this adventure, I want to (in the Report Footer) be able to do
something like this:

BalanceDue =
sum(ProductTotal)+sum(SalesTax)+sum(DeliveryCharge)-sum(AmountPaid)

Any help?

Thanks,

/\nth

 

[Marshall Barton]

Here are the tables involved:

=======Orders=========
OrderID
SalesTax (amount, not rate)
ProductTotal
DeliveryCharge
AmountPaid

=====Order Details=======
OrderID
Product
Price
Qty
Discount
======================

I use a query to join the two and that is the basis for my report. In the
Detail section of the report, I have the fields from Order Details. The
fields from the Orders table are in the OrderID Footer section. So far, no
problem. In the Report Footer, I want to sum the fields from the Orders
table. That doesn't work because in the query each row contains the
DeliveryCharge for the entire order. Therefore, =sum(deliverycharge)
doesn't give me the total of all orders' delivery charges. Instead, it
gives me sum(DeliveryCharge * # of items on Order). How do I fix this?

At the end of this adventure, I want to (in the Report Footer) be able to do
something like this:

BalanceDue =
sum(ProductTotal)+sum(SalesTax)+sum(DeliveryCharge)-sum(AmountPaid)

I have a bad feeling about your table structure. How do you
figure out the value in the ProductTotal field? Shouldn't
that be calculated from the records in OrderDetails to
guarantee that the two amounts are the same? Isn't there
the same issue with the tax amount? I also suggest that you
should have a payments table so you can deal with partial
payments and other situations.

But, back to your question. Add duplicate text boxes for
the product total and tax values in the Orders group footer,
but this time set their Running Sum property to Over All.
Now, the report footer can refer to the running sum values
to display the total for all orders in the report.

 

[Anth Stever]

For reasons of his own, the owner of this database wants to have the
ProductTotal in the orders table (the field is populated programmatically on
the Orders form and updated whenever the price, quantity, or discount of any
line item is changed) even though theoretically once could calculate it from
OrderDetails on demand. However, the database is quite large (10's of
thousands of records, DB size often in the neighborhood of 80MB) and doing
that calculation takes time - putting the ProductTotal in the Orders table
speeds that process.

You're right that the issue is the same for the SalesTax (and the
AmountPaid). Frankly, I thought I had already dealt with this by putting
code in OnPrint to do my running totals. It's possible that the owner
somehow reverted to an older version of the DB and has accidentally
therefore bypassed that code. When I did your suggestion with my copy of
the DB (with the code intact), the report totals were identical. I'll have
to spend some time on-site to be sure about this.

Thanks for the reply.

 

[Marshall Barton]

"the report totals were identical" to what? Did you get the
right results?

Watch out when you use code in report event procedures to
accumulate a total value, it is not a reliable approach.
There are circumstance that can cause the code to run
multiple times for the same data.