Hide zeros in totals of crosstab query

G

Guest

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;
 
J

John Spencer

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 said:
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;
Click to expand...
 
Top