B
Bruce
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;
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;