Subtotals in reports

[wizard_chef]

I have a problem that is probably due to a poorly designed set of
tables, but now I have a rather large set of data in the database that
I do not wish to disturb. This is a typical customer-purchase database
similar to the one in Northwind. The snarl comes in the way shipping is
handled. If a customer makes 3 purchases that are shipped, each entry
in the OrderDetails contains the shipping charge. This means any query
that pulls out a subset of the orders will show shipping on each item
in a shipment.

I am trying to design a report that shows for each purchase date and
for each customer on that date, an itemized list of the items, thier
cost (quantity*unitprice), and the shipping.

I have tried severals ways to group the report, and the closest I can
get is to have a footer on the purchase date, since any purchase by the
customer on a given date will be logged as a single shipment (hence,
single shipping charge). I can get the subtotals for each date
(customer, item, cost, etc.) to work fine. When I try to get a report
total, if I use =sum([quantity*unitcost])+freight, it undercounts the
freight charges. If I use -sum([quantity*unitcost]+freight), it
overcounts the freight cost.

If I could simply sum the subtotals, I would have the correct answer,
but since these are calculated, I can't do that. I also cannot think of
a way to modify the query to make the task easier.

A script that would go in an examine each date and only include the
shipping cost once for a customer's order on a given date would work
fine, but I don't know how to do that.

Suggestions, anyone?

Oh, here is the SQL code (built by Access, not by me!)

SELECT Payments.PaymentMethodID, Orders.OrderDate,
Payments.DepositDate, Products.ProductName, Orders.FreightCharge,
[Order Details].Quantity, [Order Details].UnitPrice,
Customers.ContactFirstName, Customers.ContactLastName
FROM Products INNER JOIN (((Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) INNER JOIN Payments ON
Orders.OrderID = Payments.OrderID) ON Products.ProductID = [Order
Details].ProductID
WHERE (((Payments.PaymentMethodID)=4) AND
((Payments.DepositDate)=[Input Deposit Date]));

 

[Marshall Barton]

I have a problem that is probably due to a poorly designed set of
tables, but now I have a rather large set of data in the database that
I do not wish to disturb. This is a typical customer-purchase database
similar to the one in Northwind. The snarl comes in the way shipping is
handled. If a customer makes 3 purchases that are shipped, each entry
in the OrderDetails contains the shipping charge. This means any query
that pulls out a subset of the orders will show shipping on each item
in a shipment.

I am trying to design a report that shows for each purchase date and
for each customer on that date, an itemized list of the items, thier
cost (quantity*unitprice), and the shipping.

I have tried severals ways to group the report, and the closest I can
get is to have a footer on the purchase date, since any purchase by the
customer on a given date will be logged as a single shipment (hence,
single shipping charge). I can get the subtotals for each date
(customer, item, cost, etc.) to work fine. When I try to get a report
total, if I use =sum([quantity*unitcost])+freight, it undercounts the
freight charges. If I use -sum([quantity*unitcost]+freight), it
overcounts the freight cost.

If I could simply sum the subtotals, I would have the correct answer,
but since these are calculated, I can't do that. I also cannot think of
a way to modify the query to make the task easier.

Add a text box named txtRunFreight to the purchase date
footer section. Set its control source to Freight and
Running Sum property to Over All.

Then the report footer can display the grand total by using
the expression:
=Sum([quantity*unitcost]+txtRunFreight

 

[wizard_chef]

I have a problem that is probably due to a poorly designed set of
tables, but now I have a rather large set of data in the database that
I do not wish to disturb. This is a typical customer-purchase database
similar to the one in Northwind. The snarl comes in the way shipping is
handled. If a customer makes 3 purchases that are shipped, each entry
in the OrderDetails contains the shipping charge. This means any query
that pulls out a subset of the orders will show shipping on each item
in a shipment.

I am trying to design a report that shows for each purchase date and
for each customer on that date, an itemized list of the items, thier
cost (quantity*unitprice), and the shipping.

I have tried severals ways to group the report, and the closest I can
get is to have a footer on the purchase date, since any purchase by the
customer on a given date will be logged as a single shipment (hence,
single shipping charge). I can get the subtotals for each date
(customer, item, cost, etc.) to work fine. When I try to get a report
total, if I use =sum([quantity*unitcost])+freight, it undercounts the
freight charges. If I use -sum([quantity*unitcost]+freight), it
overcounts the freight cost.

If I could simply sum the subtotals, I would have the correct answer,
but since these are calculated, I can't do that. I also cannot think of
a way to modify the query to make the task easier.

Add a text box named txtRunFreight to the purchase date
footer section. Set its control source to Freight and
Running Sum property to Over All.

Then the report footer can display the grand total by using
the expression:
=Sum([quantity*unitcost]+txtRunFreight

Marshall, Marshall!!! You don't know how many strands of hair this
little thing has caused me. Actually, the Running Total selection I
needed was "Over Group" due to the way I had my report set up, but
voila! I now have a running total for multiple orders by a customer on
a particular date, and the report total is correct for the first time.
I always wondered what those options were good for. Now I know.

Many, many thanks! You have made my week.

wizard_chef (who should stick to the kitchen)