Re: Hide zeros in totals of crosstab query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You need to put the criteria for the Sum in a HAVING clause. The WHERE
clause applies in selecting the records to aggregate; the HAVING clause
applies after the aggregation has taken place.

TRANSFORM IIf(Sum([tbl_FCSTDET]![Weight(kg)]) Is
Null,0,Sum([tbl_FCSTDET]![Weight(kg)]/1000)) AS [Volume(kg)]
SELECT tbl_FCSTHDR.PERIOD, tbl_ITEM_MASTER.ProductID,
tbl_ITEM_MASTER.Description, tbl_ITEM_MASTER.[Net Wt (kg) / case],
Category.GroupID, Sum([tbl_FCSTDET]![Weight(kg)])/1000 AS Total
FROM tbl_FCSTHDR INNER JOIN (tbl_BUYING_GROUPS INNER JOIN (tbl_SourceCountry
INNER JOIN (Category INNER JOIN ((tbl_ITEM_MASTER INNER JOIN tbl_FCSTDET ON
tbl_ITEM_MASTER.ProductID = tbl_FCSTDET.ProductID) INNER JOIN
tbl_SourcePlant
ON tbl_ITEM_MASTER.Shipped_From = tbl_SourcePlant.SourcePlantID) ON
Category.CatID = tbl_ITEM_MASTER.CatID) ON tbl_SourceCountry.SourceCountryID
= tbl_SourcePlant.SourceCountryID) ON tbl_BUYING_GROUPS.BuyingGroupCode =
tbl_FCSTDET.BuyingGroupCode) ON tbl_FCSTHDR.ID = tbl_FCSTDET.ID
WHERE tbl_FCSTDET.Fcst_Month Between 1 And 12
AND tbl_FCSTHDR.Current=True
AND tbl_BUYING_GROUPS.AP_Demand=True
GROUP BY tbl_FCSTHDR.PERIOD, tbl_ITEM_MASTER.ProductID,
tbl_ITEM_MASTER.Description, tbl_ITEM_MASTER.[Net Wt (kg) / case],
Category.GroupID
HAVING Sum([tbl_FCSTDET]![Weight(kg)])/1000)<>0
PIVOT tbl_BUYING_GROUPS.COUNTRY;

"Bruce" <Bruce@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BE997E11-369B-49E3-B710-AFDBD222F681@xxxxxxxxxxxxxxxx
>I have a crosstab query with a sum of the values as a row heading called
> TOTAL. In other words if my columns are 1 through 12 then the 'total'
> field
> reports the sum of 1 - 12 for each record. So far no problems.
>
> Th bit I am having trouble with is some of the totals equal zero which I
> want to filter, possibly by setting criteria to <>0 etc. When I do this
> Access tells me that I 'Cannot have an agregate function in the where
> clause.
>
> How can I get round this? Below is my SQL with adn without the <>0
> condition.
>
> Bruce
>
>
> 'Without
>
> TRANSFORM IIf(Sum([tbl_FCSTDET]![Weight(kg)]) Is
> Null,0,Sum([tbl_FCSTDET]![Weight(kg)]/1000)) AS [Volume(kg)]
> SELECT tbl_FCSTHDR.PERIOD, tbl_ITEM_MASTER.ProductID,
> tbl_ITEM_MASTER.Description, tbl_ITEM_MASTER.[Net Wt (kg) / case],
> Category.GroupID, Sum([tbl_FCSTDET]![Weight(kg)])/1000 AS Total
> FROM tbl_FCSTHDR INNER JOIN (tbl_BUYING_GROUPS INNER JOIN
> (tbl_SourceCountry
> INNER JOIN (Category INNER JOIN ((tbl_ITEM_MASTER INNER JOIN tbl_FCSTDET
> ON
> tbl_ITEM_MASTER.ProductID = tbl_FCSTDET.ProductID) INNER JOIN
> tbl_SourcePlant
> ON tbl_ITEM_MASTER.Shipped_From = tbl_SourcePlant.SourcePlantID) ON
> Category.CatID = tbl_ITEM_MASTER.CatID) ON
> tbl_SourceCountry.SourceCountryID
> = tbl_SourcePlant.SourceCountryID) ON tbl_BUYING_GROUPS.BuyingGroupCode =
> tbl_FCSTDET.BuyingGroupCode) ON tbl_FCSTHDR.ID = tbl_FCSTDET.ID
> WHERE (((tbl_FCSTDET.Fcst_Month) Between 1 And 12) AND
> ((tbl_FCSTHDR.Current)=True) AND ((tbl_BUYING_GROUPS.AP_Demand)=True))
> GROUP BY tbl_FCSTHDR.PERIOD, tbl_ITEM_MASTER.ProductID,
> tbl_ITEM_MASTER.Description, tbl_ITEM_MASTER.[Net Wt (kg) / case],
> Category.GroupID
> PIVOT tbl_BUYING_GROUPS.COUNTRY;
>
>
> 'With
>
> TRANSFORM IIf(Sum([tbl_FCSTDET]![Weight(kg)]) Is
> Null,0,Sum([tbl_FCSTDET]![Weight(kg)]/1000)) AS [Volume(kg)]
> SELECT tbl_FCSTHDR.PERIOD, tbl_ITEM_MASTER.ProductID,
> tbl_ITEM_MASTER.Description, tbl_ITEM_MASTER.[Net Wt (kg) / case],
> Category.GroupID, Sum([tbl_FCSTDET]![Weight(kg)])/1000 AS Total
> FROM tbl_FCSTHDR INNER JOIN (tbl_BUYING_GROUPS INNER JOIN
> (tbl_SourceCountry
> INNER JOIN (Category INNER JOIN ((tbl_ITEM_MASTER INNER JOIN tbl_FCSTDET
> ON
> tbl_ITEM_MASTER.ProductID = tbl_FCSTDET.ProductID) INNER JOIN
> tbl_SourcePlant
> ON tbl_ITEM_MASTER.Shipped_From = tbl_SourcePlant.SourcePlantID) ON
> Category.CatID = tbl_ITEM_MASTER.CatID) ON
> tbl_SourceCountry.SourceCountryID
> = tbl_SourcePlant.SourceCountryID) ON tbl_BUYING_GROUPS.BuyingGroupCode =
> tbl_FCSTDET.BuyingGroupCode) ON tbl_FCSTHDR.ID = tbl_FCSTDET.ID
> WHERE (((Sum([tbl_FCSTDET]![Weight(kg)])/1000)<>0) AND
> ((tbl_FCSTDET.Fcst_Month) Between 1 And 12) AND
> ((tbl_FCSTHDR.Current)=True)
> AND ((tbl_BUYING_GROUPS.AP_Demand)=True))
> GROUP BY tbl_FCSTHDR.PERIOD, tbl_ITEM_MASTER.ProductID,
> tbl_ITEM_MASTER.Description, tbl_ITEM_MASTER.[Net Wt (kg) / case],
> Category.GroupID
> PIVOT tbl_BUYING_GROUPS.COUNTRY;
>
>
>


.

Relevant Pages

  • Re: Not Exists joining 2 tables
    ... "'code' is a bad name for a key column" is a valid complaint. ... EXISTS clause with a correlated subquery properly, ... SQL+ syntax and start using the ANSI SQL syntax that seems ... Then the only criteria in the where clause ...
    (comp.databases.ms-sqlserver)
  • Re: Criteria linked to form combo Yes/No or All expression help
    ... The way I'd debug it would be to start w/ one OR clause in the criteria. ... Run the query & see if it works for that criteria. ... I followed your suggestion of pasting the sql, ...
    (microsoft.public.access.queries)
  • RE: build the where clause...
    ... Looks like you are adding a 'where' clause for every item instead of just the ... It might help to display the SQL for your query just before you execute it. ... (even though I know there are records with both search criteria used). ... With one criteria debug.print strWhere yields: ...
    (microsoft.public.access.formscoding)
  • Re: Access Group by and Count problem
    ... This criterion should be put in the WHERE clause, since it has nothing to do ... Criteria in the WHERE clause are enforced BEFORE ... GROUP BY PageVisited, StartTime ... in the grid, instead of one of the aggregation functions. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Select query populating list box
    ... Additional assumption is that txtInstitution is never null. ... Every time you add one more criteria to the where clause, ... However if you look at my original code the text control needs to search ...
    (microsoft.public.access.queries)