Re: Subtotals in reports

wizard_chef wrote:

>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

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: Subtotals in reports
    ... If a customer makes 3 purchases that are shipped, ... >>in the OrderDetails contains the shipping charge. ... >>I have tried severals ways to group the report, ... >>get is to have a footer on the purchase date, ...
    (microsoft.public.access.reports)
  • Re: Adding aggregated columns to a report
    ... What I would do is to insert a group based on the customer. ... Insert all of your fields into the group footer. ... > I have a DataSet with 2 tables which is the source for my report. ... > or the date of the last purchase etc.. ...
    (microsoft.public.vb.crystal)
  • 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: Adding multiple series to charts
    ... instead show days since the first purchase. ... As soon as the script detects a change in the customer ... Jon Peltier, Microsoft Excel MVP ... on the x axis and number of widgets purchased on the y axis. ...
    (microsoft.public.excel.charting)
  • Re: Frustration with lack of ability to get warranty work done by Miller authorized service centers.
    ... | I knew I shouldn't have done this, but I purchased a Miller welder online ... | covered under warranty, they will send a replacement, with a turn-around ... and it behooves him to keep you as his customer. ... stores, and they know it all too well, especially with your recent purchase. ...
    (rec.crafts.metalworking)
Loading