Yeah. Sorry, I should have tested the solution. Try using two queries.
First query would get the data and the second would use that saved query as
the source for the crosstab query. This suggestion is also untested.
QueryOne would be a Summary query. Save this query
SELECT Orders.EmployeeID, Year(OrderDate) as OrderYear
, Count(Orders.OrderID) AS CountOrders
FROM Orders
WHERE Year(OrderDate) in (1997,1998)
GROUP BY Orders.EmployeeID
HAVING Count(Orders.OrderId) > 100
QueryTwo would use that as the source for the crosstab
TRANSFORM First(CountOrders)
SELECT EmployeeID, OrderYear, Sum(CountOrders) as TotalOrders
FROM QueryOne
GROUP BY EmployeeID, OrderYear
PIVOT OrderYear
You ***MIGHT*** be able to cram that all into one query. But even if I were
doing so, I would probably build query one and get it working correctly,
then I would build query two and get it working. Once they were both
working, I would copy query two, open it in SQL view, and paste the SQL view
(minus the ; at the end) into the copy, so I ended up with something that
looked like the following (without the nice formatting).
TRANSFORM First(CountOrders)
SELECT EmployeeID, OrderYear, Sum(CountOrders) as TotalOrders
FROM
(SELECT Orders.EmployeeID, Year(OrderDate) as OrderYear
, Count(Orders.OrderID) AS CountOrders
FROM Orders
WHERE Year(OrderDate) in (1997,1998)
GROUP BY Orders.EmployeeID
HAVING Count(Orders.OrderId) > 100
) As QueryOne
GROUP BY EmployeeID, OrderYear
PIVOT OrderYear
TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT Orders.EmployeeID, Count(Orders.OrderID) AS [Total Of OrderID]
FROM Orders
WHERE Year(OrderDate) in (1997,1998)
GROUP BY Orders.EmployeeID
HAVING Count(Orders.OrderId) > 100
PIVOT Format([OrderDate],"yyyy");
Hanson said:
John Spencer, thank you very much for your help.
I think you understood my problems. But it stil has error message:'Syntex
error in TRANSFORM statement". It seem that TRANSFORM does not suppor
HAVING
clause. I am using MS Access 2002. anyone can help please.
Hanson
John Spencer said:
You need a WHERE clause to limit the years that are used to get the data
and
a Having clause to limit the return based on the count of the records
returned being more than 100. However, this may still fail to give you
what
you want - since I am a bit unsure of the exact thing you want returned.
TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT Orders.EmployeeID, Count(Orders.OrderID) AS [Total Of OrderID]
FROM Orders
WHERE Year(OrderDate) in (1997,1998)
GROUP BY Orders.EmployeeID
HAVING Count(Orders.OrderId) > 100
PIVOT Format([OrderDate],"yyyy");
I am a beginer in MS Access. I am learning to creat a filter for
crosstab
queries. This is the crosstab query from "Order table" in NORTHWIND
database.
TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT Orders.EmployeeID, Count(Orders.OrderID) AS [Total Of OrderID]
FROM Orders
GROUP BY Orders.EmployeeID
PIVOT Format([OrderDate],"yyyy");
Employee Total Of OrderID 1996 1997 1998
Davolio, Nancy 123 26 55 42
Fuller, Andrew 96 16 41 39
Leverling, Janet 127 18 71 38
Peacock, Margaret 156 31 81 44
Buchanan, Steven 42 11 18 13
Suyama, Michael 67 15 33 19
King, Robert 72 11 36 25
Callahan, Laura 104 19 54 31
Dodsworth, Anne 43 5 19 19
Please help me to set filter criterial: (Total Of OrderID > 100) and
(years
= 1997&1998) so that the output will be:
Employee Total Of OrderID 1997 1998
Leverling, Janet 109 71 38
Peacock, Margaret 125 81 44
Thanks for your help.
