I'm attempting a crosstab query for a fiscal year period. Want to display column headings as 'Jan', 'Feb', etc.

Correct: Returns Column Headings 1 – 12

TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT FirstName & " " & LastName AS FullName
FROM Employees1 INNER JOIN Orders ON Employees1.EmployeeID = Orders.EmployeeID
WHERE (((Orders.OrderDate) Between #4/1/1995# And #3/31/1996#))
GROUP BY FirstName & " " & LastName
ORDER BY FirstName & " " & LastName
PIVOT DatePart("m",OrderDate);

This, identical except for the pivot statement returns only two
Columns—‘Dec’ & ‘Jan’. ‘Dec’ holds return from Column 1 (above).
‘Jan’ is a total of Columns 2 – 12 (above). I'm at a loss how that works.

TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT FirstName & " " & LastName AS FullName
FROM Employees1 INNER JOIN Orders ON Employees1.EmployeeID = Orders.EmployeeID
WHERE (((Orders.OrderDate) Between #4/1/1995# And #3/31/1996#))
GROUP BY FirstName & " " & LastName
ORDER BY FirstName & " " & LastName
PIVOT Format(Month(OrderDate),"mmm");

I've created a function that returns properly sequenced months, based on
the start date, e.g.:
('Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar')
and attempted to incorporate this output in the Pivot statement but haven't been able to devise a syntax that will process

Any thoughts appreciated.

Best wishes - Bob

Shouldn't it be PIVOT your function ?

Hi Rich-

Thanks for the response.

Actually I'm using code (not posted since that's not the problem) to create the querydef. It only calls for the start date, and then creates the BETWEEN mm/dd/yyyy and mm/dd/yyyy statement and the months string shown in the initial post. It then creates the code, inserting the date and months statements. My problem is getting the proper syntax for the pivot statement.

Bob

Finally got it.

TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT FirstName & " " & LastName AS FullName
FROM Employees1 INNER JOIN Orders ON Employees1.EmployeeID = Orders.EmployeeID
WHERE (((Orders.OrderDate) Between #4/1/1995# And #3/31/1996#))
GROUP BY FirstName & " " & LastName
ORDER BY FirstName & " " & LastName
PIVOT Format(OrderDate,"mmm") IN('Apr','May','Jun','Jul','Aug','Sep','Oct','Nov' ,'Dec','Jan','Feb','Mar');

Have done numerous spot checks and the results are in the correct months.

Bob