Subtotals in reports

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]));

.

Relevant Pages

  • Re: Shopping a pin... DIY, or pay a pro?
    ... shipping in and shipping out which is a huge hassle, cost, and risk as well. ... We used to do work like this on a few games years ago when we weren't as ... always give the customer a written estimate and specifics on what will be ...
    (rec.games.pinball)
  • Re: McGyver resource: The Circular File
    ... I will want a free - or low cost ... a non-starter because the cost of shipping tends to be some huge ... The only reason that a Circular File entry would fall off the list ... Nichols horizontal mill. ...
    (rec.crafts.metalworking)
  • Re: Powersellers get away with MURDER!
    ... > Bizrate isn't one of your better rating services. ... For example, when I look at Buy.com's complaints, most of them seem ... to be about slow shipping or Buy.com's use of USPS--which is often the same ... customer, and has the same effect on the overall rating. ...
    (alt.marketing.online.ebay)
  • Re: what would shipping class be for a pin? SHIPPING INFO. HELP
    ... the lower the cost. ... We get iron castings shipped every day at class 50, ... This won't work for international shipping though, ...
    (rec.games.pinball)
  • Re: Migrating SBS servers
    ... I have not passed on the cost of a MS or any other support ... I think the customer pays me for a solution to the problem. ... wanted to pay MS, they could have called MS themselves. ...
    (microsoft.public.windows.server.sbs)
Loading