Re: Incorrect subtotals in a report

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Duane Hookom (duanehookom_at_NoSpamHotmail.com)
Date: 09/23/04 

Date: Thu, 23 Sep 2004 10:55:59 -0500

There is really no limit to the number of row headings in a crosstab. There
is a limit of 255 columns in any table or query. If you want multiple values
for columns then you can use a solution as this solution from an earlier
posting (below). There are samples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

You can create multiple values by combining a crosstab and cartesian query.

Create a sample using the Northwind database to create both a Quantity and
Quantity * Price value for each column.
-Create a new table tblXtabColumns with a single field [FldName]
-add two records to this table
    "Quantity"
    "QtyXPrice"
-create a crosstab with the table [Products], [Orders], [Order Details], and
[tblXtabColumns]
-join the first three tables as expected but don't join tblXtabColumns to
any other table
-your Column Heading expression is
    Expr1:[FldName] & Month([OrderDate])
-your Value expression is
    DaVal:IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])
 Sum
-I set OrderDate for the first three months of 1998
The full SQL is:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

You will get two columns per month QtyXPriceN and QuantityN. 

-- 
Duane Hookom
MS Access MVP
--
"Jedster" <Jedster@discussions.microsoft.com> wrote in message
news:0B6E9C9F-D420-4AC0-AFD3-3ADCDA8BC4D8@microsoft.com...
> Duane,
>
> Thanks for your reply, however, that doesn't seem to solve my problem.
> Rather, what I'm looking to do is create something like a crosstab query
> (although the standard method of this doesn't seem to allow sufficient row
> headings!).  Basically, the report needs to look something like this:
>
> Week Number x,
> Buyer: Joe Bloggs,
> Department 01,
> Section 01,
> Subsection 01,
>
> Product A, Total quantity of sales for Sunday (Weekday 1),...and so on
> to...Total quantity of sales for Saturday (Weekday 7), Week total quantity
of
> sales, Week total value of sales.
>
> Product B, Total quantity of sales for Sunday, etc., until,...
>
> Subsection 01 subtotals for all of the above quantities.
>
> Then Products and subtotals for Subsection 02 etc., until...
>
> Section 01 subtotals for all of the above quantities.
>
> Then Products and subtotals for all Subsections under Section 02, etc.,
> until...
>
> Department 02 (no Department subtotals are necessary), and so on and so
forth.
>
> The difficulty I'm finding is trying to summarise the total sales for the
> week, whilst still being able to list the daily sales by product, within
the
> same report.
>
> Thanks again,
>
> Jedster
>
>
> "Duane Hookom" wrote:
>
> > If I understand what you need, you can try something like:
> > =Sum(Abs(...Your Expression...) * [ValueToSum])
> > or maybe
> >  =Sum(Abs([Weekday]=1) * [SalesQuantity])
> >
> > -- 
> > Duane Hookom
> > MS Access MVP
> >
> >
> > "Jedster" <Jedster@discussions.microsoft.com> wrote in message
> > news:4930BCF1-600E-4EC0-B526-6405C758BA30@microsoft.com...
> > > I am trying to create a report based on a flat file to show the
cumulative
> > > sales by day of the week.  The problem I am now having is that I
cannot
> > get
> > > some of my subtotals to calculate correctly.
> > >
> > > The table I am using contains the total daily sales quantities for
each
> > > product, together with the corresponding product details.  I have then
> > > created a simple query on this to produce the following fields.  These
are
> > > listed with samples of the data contained:
> > >
> > > Weekday (to identify the day number) eg. 1, 2, 3
> > > Day, eg. Sun, Mon, Tue
> > > WeekNo, eg. 31, 32, 33
> > > DepartmentCode, eg, 94, 95, 96
> > > DepartmentDesc, eg. Homewares, Ambient, Grocery
> > > SectionCode, eg. 41, 42, 43
> > > SectionDesc, eg. Cookware, Tinned, Fresh produce
> > > SubsectionCode, eg. 02, 03, 04
> > > SubsectionDesc, eg. Pans, Baked beans, Tomatoes
> > > BuyerName, eg. James Richardson, Tim Reynolds, Bob Taylor
> > > ProductCode, eg. 415745, 254471, 958764
> > > ProductDesc, eg, Stainless steel 24cm, Heinz 450g, Spanish pack of 6
> > > SalesQuantity, eg, 4, 258, 65
> > > SalesValue, eg. 30.66, 64.50, 45.50
> > >
> > > Next, I based a report on this query and grouped it in order of
WeekNo,
> > > BuyerName, DepartmentCode, SectionCode, SubsectionCode and
ProductCode.
> > >
> > > In the ProductCode footer, I created a text box to show the total
sales
> > for
> > > day 1 (Sunday) through to Saturday by using variants of the following
> > formula:
> > >
> > > =IIf([Weekday]=1,[SalesQuantity],0)
> > >
> > > To the right of these, I created two boxes with the following criteria
to
> > > show the total sales values for the week:
> > >
> > > =Sum([SalesQuantity])
> > > =Sum([SalesValue])
> > >
> > > These 9 fields were then copied into two footers I had created for the
> > > SubsectionCode and SectionCode.  I am now left with the weekly totals
that
> > > appear to be correct, whilst the daily totals are incorrect.
> > >
> > > I think the problem here lies with the fact that I need to somehow
> > summarise
> > > the data by week, but also retain the 'day' information in order to
> > produce
> > > the daily totals.  Unfortunately, my experience with Access is
> > insufficient
> > > to know whether this is in any way possible.
> > >
> > > Any assistance offered will be much appreciated.
> > >
> > > Thanks in advance.
> >
> >
> >

Relevant Pages

  • Re: a query to produce sales activity totals
    ... The query generates a row for each salesperson, ... Allen Browne - Microsoft MVP. ... of the sales process, including the number of sold and delivered vehicles. ... put these totals into a spread sheet that shows each salesperson's ...
    (microsoft.public.access.queries)
  • Re: Incorrect subtotals in a report
    ... Product A, Total quantity of sales for Sunday,...and so on ... Subsection 01 subtotals for all of the above quantities. ... Then Products and subtotals for Subsection 02 etc., ... whilst the daily totals are incorrect. ...
    (microsoft.public.access.reports)
  • Re: Sql query to count how many sales between dates
    ... be less efficient than using a Totals query. ... table to the sales table in the query, as you should be, then set up a LEFT ... JOIN from the employee table to the sales table, ... StartDateControl with the correct name of the control where you enter the ...
    (comp.databases.ms-access)
  • Re: a query to produce sales activity totals
    ... In query design view, depress the Total button on the toolbar Access adds a Total row to the query design grid. ... number of contacts and the varous steps they have taken in the sales process. ... put these totals into a spread sheet that shows each salesperson's numbers... ...
    (microsoft.public.access.queries)
  • Re: a query to produce sales activity totals
    ... creating of the query to spit out my data that's my challenge. ... I'm not clear how your sales quantities actually relate to the steps of the ... I need a query or report of some sort to give me the ... I manually count each field so I can enter the totals on an excel ...
    (microsoft.public.access.queries)