I've created a crosstab query and I need to get the totals by
quarter. It
works but I can't seem to get the quarters in order. For example
right now
the query goes:

q1 2008 q1 2009 q2 2008 q2 2009

I added a date field to sort on and it's not working. Here is the sql
for
the query:

TRANSFORM Sum(SalesOrdersComplete.QtyOrdered) AS [SumOfQty Ordered]
SELECT SalesOrdersComplete.ItemID AS [Item ID],
ItemsList.ItemDescription AS
[Item Description], Sum(SalesOrdersComplete.QtyOrdered) AS [Total Of
Qty
Ordered]
FROM SalesOrdersComplete LEFT JOIN ItemsList ON
SalesOrdersComplete.ItemID =
ItemsList.ItemID
WHERE (((SalesOrdersComplete.SO_Date) Between #1/1/2008# And
#12/31/2009#))
GROUP BY SalesOrdersComplete.ItemID, ItemsList.ItemDescription,
SalesOrdersComplete.SO_Date
ORDER BY SalesOrdersComplete.ItemID, SalesOrdersComplete.SO_Date
PIVOT Format([SO_Date],"\qq yyyy");

I need this to sort by quarter and year. Let me know if you need any
more
information. Any help would be appreciated.

Change the pivot clause to
PIVOT Format([SO_Date],"yyyy \qq")

OR if the years and quarters are fixed then use the IN operator to specify the
column names and the order.

PIVOT Format([SO_Date],"\qq yyyy") IN ("q1 2008","q2 2008","q3 2008","q4
2008","q1 2009","q2 2009","q3 2009","q4 2009")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 12/17/2010 10:57 AM, Ecovindaloo wrote: