DatePart (1 Viewer)

[Seajay]

I'm working with a crosstab query and am trying to sort by quarter for every quarter after 1/1/99 up until now. This is proving more difficult then I had originally thought and would appreciate any help.
The layout I was hoping for was with the year dates above them.

Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4

If I could just get a quarterly summary though I'd be content.

Thanks

[This message has been edited by Seajay (edited 07-27-2001).]

 

[raskew]

Northwind's Quarterly Orders By Product crosstab query seems to come close to what you've described.

To spread it out, quarter by quarter, try copying/pasting these two queries into Northwind:

Query11:
SELECT Products.ProductName, Year([OrderDate]) AS theyear, DatePart("q",[OrderDate]) AS theqtr, Sum([Order Details].[UnitPrice]*[Quantity]) AS net, [theyear] & "-" & [theqtr] AS myqtr
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
GROUP BY Products.ProductName, Year([OrderDate]), DatePart("q",[OrderDate]);


Query11_Crosstab:
TRANSFORM Sum([net]) AS [The Value]
SELECT [ProductName], Sum([net]) AS [Total Of net]
FROM Query11
GROUP BY [ProductName]
PIVOT [myqtr];