Datepart for Quarter

[Guest]

I am using the following SQL for the Quarter,

SELECT (Format([Date],"q"" '""yy")) AS Expr1,
Sum([ICD Query].[Bragg Main]) AS [Fort Bragg Main],
Sum([ICD Query].[82nd]) AS [82nd ABN DIV],
Sum([ICD Query].Total) AS Total
FROM [ICD Query]
GROUP BY (Format([Date],"q"" '""yy")),
(Year([Date])*4+DatePart("q",[Date])-1);

I would like the quarter to be displayed as 1Q05 can this be done?
Also I need to order By date. With this formula, it is ordering like;
1' 05
1' 06
2' 05
3' 05

etc...

Please help

 

[John Spencer]

SELECT (Format([Date],"q\Qyy")) AS Expr1,
Sum([ICD Query].[Bragg Main]) AS [Fort Bragg Main],
Sum([ICD Query].[82nd]) AS [82nd ABN DIV],
Sum([ICD Query].Total) AS Total
FROM [ICD Query]
GROUP BY Format([Date],"q\Qyy"), Format([Date],"yyyyq")
ORDER BY Format([Date],"yyyyq")

 

[John Spencer]

Whoops,
In a report!!!

You will need to include the quarter and year in the group by statement and
then use the report's sorting and grouping properties to get the correct
sort order

SELECT Year([Date]) as TheYear,
DatePart("q",[Date]) as TheQuarter,
Format([Date],"q\Qyy") AS Expr1,
Sum([ICD Query].[Bragg Main]) AS [Fort Bragg Main],
Sum([ICD Query].[82nd]) AS [82nd ABN DIV],
Sum([ICD Query].Total) AS Total
FROM [ICD Query]
GROUP BY Year([Date]) as TheYear,
DatePart("q",[Date]) as TheQuarter
Format([Date],"q\Qyy")