Filter in Crosstab query

G

Guest

Hi,

Using Access NT I need to filter all records out that are less than 10 in
the total the query I have is as follows:-

TRANSFORM IIf(IsNull(Sum([Qty])),0,Sum([Qty])) AS SumOfTotal_Count
SELECT tblPurchaser_1.Purchaser_Code, Sum(tblTransData.Qty) AS Total
FROM tblSupplierShort INNER JOIN (tblTransData INNER JOIN (tblPurchaser
INNER JOIN tblPurchaser AS tblPurchaser_1 ON tblPurchaser.Purchaser_Parent =
tblPurchaser_1.Purchaser_ID) ON tblTransData.Agency =
tblPurchaser.Purchaser_ID) ON tblSupplierShort.SupplierLongID =
tblTransData.Supplier
WHERE (((tblTransData.Type)="GSM") AND ((tblTransData.Supplier)=196 Or
(tblTransData.Supplier)=271 Or (tblTransData.Supplier)=293) AND
((tblTransData.Date) Between #7/1/2005# And #7/31/2005#))
GROUP BY tblPurchaser_1.Purchaser_Code
PIVOT tblSupplierShort.Supplier_Short;


hoping that u can help

Thanks

Trev
 
M

[MVP] S.Clark

I typically will break this type of situation into several queries. For
example, do everything you need total wise in one query, then in a 2nd
query, perform the criteria and crosstab. If all else fails, write the
needed data to a temp table and crosstab from there.
 
D

Duane Hookom

Try:
TRANSFORM IIf(IsNull(Sum([Qty])),0,Sum([Qty])) AS SumOfTotal_Count
SELECT tblPurchaser_1.Purchaser_Code, Sum(tblTransData.Qty) AS Total
FROM tblSupplierShort INNER JOIN (tblTransData INNER JOIN (tblPurchaser
INNER JOIN tblPurchaser AS tblPurchaser_1 ON tblPurchaser.Purchaser_Parent =
tblPurchaser_1.Purchaser_ID) ON tblTransData.Agency =
tblPurchaser.Purchaser_ID) ON tblSupplierShort.SupplierLongID =
tblTransData.Supplier
WHERE (((tblTransData.Type)="GSM") AND ((tblTransData.Supplier)=196 Or
(tblTransData.Supplier)=271 Or (tblTransData.Supplier)=293) AND
((tblTransData.Date) Between #7/1/2005# And #7/31/2005#))
GROUP BY tblPurchaser_1.Purchaser_Code
HAVING Sum(tblTransData.Qty) < 10
PIVOT tblSupplierShort.Supplier_Short;
 
Top