Crosstab Where Clause Problem

[Guest]

Can I put a where clause on a cross tab? The following doesn't restrict any
items that are tabulated:

TRANSFORM Count(qryALLISSUES_BASE.[Issue Reference Number]) AS [CountOfIssue
Reference Number]
SELECT qryALLISSUES_BASE.[Classification Code Name],
Count(qryALLISSUES_BASE.[Issue Reference Number]) AS [Total Of Issue
Reference Number]
FROM qryALLISSUES_BASE
WHERE (((qryALLISSUES_BASE.[Classification Code Name])<>"") AND
((qryALLISSUES_BASE.[Issue Status Name])="open") AND
((qryALLISSUES_BASE.txtISSUE_TYPE)<3)) OR (((qryALLISSUES_BASE.txtISSUE_TYPE)
Is Null))
GROUP BY qryALLISSUES_BASE.[Classification Code Name]
PIVOT qryALLISSUES_BASE.[Audit Group Name];

Do I have to create a new query to exclude the proper items?

 

[Michel Walsh]

The WHERE clause should filter the records before the group/aggregation
occurs.

In Northwind, the following query:

TRANSFORM Count(Products.ProductID) AS CountOfProductID
SELECT Products.CategoryID, Count(Products.ProductID) AS [Total Of
ProductID]
FROM Products
WHERE (((Products.ProductName)>"Z"))
GROUP BY Products.CategoryID
PIVOT Products.SupplierID;



works fine.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Based on your response, I went back and limited the where clause. I changed
it to:

WHERE (((qryALLISSUES_BASE.[Issue Status Name])="open"))

And that worked great. I got only the issues I wanted. Therefore, I went
back and rebuilt the where clause from scratch, and I was able to make a
complex where clause work.

thanks

--
Yours Fictionally, Biggles

The WHERE clause should filter the records before the group/aggregation
occurs.

In Northwind, the following query:

TRANSFORM Count(Products.ProductID) AS CountOfProductID
SELECT Products.CategoryID, Count(Products.ProductID) AS [Total Of
ProductID]
FROM Products
WHERE (((Products.ProductName)>"Z"))
GROUP BY Products.CategoryID
PIVOT Products.SupplierID;



works fine.




Hoping it may help,
Vanderghast, Access MVP

Can I put a where clause on a cross tab? The following doesn't restrict
any
items that are tabulated:

TRANSFORM Count(qryALLISSUES_BASE.[Issue Reference Number]) AS
[CountOfIssue
Reference Number]
SELECT qryALLISSUES_BASE.[Classification Code Name],
Count(qryALLISSUES_BASE.[Issue Reference Number]) AS [Total Of Issue
Reference Number]
FROM qryALLISSUES_BASE
WHERE (((qryALLISSUES_BASE.[Classification Code Name])<>"") AND
((qryALLISSUES_BASE.[Issue Status Name])="open") AND
((qryALLISSUES_BASE.txtISSUE_TYPE)<3)) OR
(((qryALLISSUES_BASE.txtISSUE_TYPE)
Is Null))
GROUP BY qryALLISSUES_BASE.[Classification Code Name]
PIVOT qryALLISSUES_BASE.[Audit Group Name];

Do I have to create a new query to exclude the proper items?

 

[Michel Walsh]

You noted that your OR is at the 'top level' of your criteria, like:


WHERE (x=9 AND y=10 AND z=11) OR a=22


so, you can have data with x<>9.



Vanderghast, Access MVP

Based on your response, I went back and limited the where clause. I
changed
it to:

WHERE (((qryALLISSUES_BASE.[Issue Status Name])="open"))

And that worked great. I got only the issues I wanted. Therefore, I went
back and rebuilt the where clause from scratch, and I was able to make a
complex where clause work.

thanks

--
Yours Fictionally, Biggles

The WHERE clause should filter the records before the group/aggregation
occurs.

In Northwind, the following query:

TRANSFORM Count(Products.ProductID) AS CountOfProductID
SELECT Products.CategoryID, Count(Products.ProductID) AS [Total Of
ProductID]
FROM Products
WHERE (((Products.ProductName)>"Z"))
GROUP BY Products.CategoryID
PIVOT Products.SupplierID;



works fine.




Hoping it may help,
Vanderghast, Access MVP

Can I put a where clause on a cross tab? The following doesn't
restrict
any
items that are tabulated:

TRANSFORM Count(qryALLISSUES_BASE.[Issue Reference Number]) AS
[CountOfIssue
Reference Number]
SELECT qryALLISSUES_BASE.[Classification Code Name],
Count(qryALLISSUES_BASE.[Issue Reference Number]) AS [Total Of Issue
Reference Number]
FROM qryALLISSUES_BASE
WHERE (((qryALLISSUES_BASE.[Classification Code Name])<>"") AND
((qryALLISSUES_BASE.[Issue Status Name])="open") AND
((qryALLISSUES_BASE.txtISSUE_TYPE)<3)) OR
(((qryALLISSUES_BASE.txtISSUE_TYPE)
Is Null))
GROUP BY qryALLISSUES_BASE.[Classification Code Name]
PIVOT qryALLISSUES_BASE.[Audit Group Name];

Do I have to create a new query to exclude the proper items?