crosstab report layout?

Hi there,

I have a query that results in the following data:

Year Month Awarded Lost Total Quotes
2008 01 43 4 105
2008 02 39 7 79
2008 03 33 1 92
2008 04 45 5 113
2008 05 41 2 95
2008 06 44 1 129
2008 07 34 2 127
2008 08 33 0 118
2008 09 1 0 6
2008 10 0 0 1
2007 11 47 4 107
2007 12 35 2 74

SQL Statement:

SELECT Year([quoteDate]) AS [Year], Nz(Format([quoteDate],"mm"),0) AS
[Month], Nz(Sum(IIf([quoteStatus]=1,1,0)),0) AS Awarded,
Nz(Sum(IIf([quoteStatus]=2,1,0)),0) AS Lost, Count(Nz([quoteID],0)) AS [Total
Quotes]
FROM tblQuoteHeader
GROUP BY Year([quoteDate]), Nz(Format([quoteDate],"mm"),0)
ORDER BY Year([quoteDate]) DESC , Nz(Format([quoteDate],"mm"),0);


I'd like to lay this out in a crosstab format so that my months run across
the top as column headers but Access won't allow more than one "value" for a
crosstab.

Is there a way for me to accomplish this outside of a crosstab or have I
written this query incorrectly that prevents me from using crosstabs?

Thanks for your help in advance.

Alan

Try this crosstab query ---

TRANSFORM (Sum(IIf([quoteStatus]=1,1,0)) & " - " &
Sum(IIf([quoteStatus]=2,1,0))) AS Expr2
SELECT Year([quoteDate]) AS [Year], Count(tblQuoteHeader.quoteID) AS [Total
Of quotes]
FROM tblQuoteHeader
GROUP BY Year([quoteDate])
ORDER BY Year([quoteDate]) DESC
PIVOT Format([quoteDate],"mmm") In ("JAN", "FEB", "MAR", "APR", "MAY",
"JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC");

Or this ---
TRANSFORM (Sum(IIf([quoteStatus]=1,1,0)) & " - " &
Sum(IIf([quoteStatus]=2,1,0))) AS Expr2
SELECT Year([quoteDate]) AS [Year], Count(tblQuoteHeader.quoteID) AS [Total
Of quotes]
FROM tblQuoteHeader
GROUP BY Year([quoteDate])
ORDER BY Year([quoteDate]) DESC
PIVOT Format([quoteDate],"mmm") & " Awarded - Lost" In ("JAN Awarded -
Lost", "FEB Awarded - Lost", "MAR Awarded - Lost", "APR Awarded - Lost", "MAY
Awarded - Lost", "JUN Awarded - Lost", "JUL Awarded - Lost", "AUG Awarded -
Lost", "SEP Awarded - Lost", "OCT Awarded - Lost", "NOV Awarded - Lost", "DEC
Awarded - Lost");

--
KARL DEWEY
Build a little - Test a little


"Alan" wrote:

> Hi there,
>
> I have a query that results in the following data:
>
> Year Month Awarded Lost Total Quotes
> 2008 01 43 4 105
> 2008 02 39 7 79
> 2008 03 33 1 92
> 2008 04 45 5 113
> 2008 05 41 2 95
> 2008 06 44 1 129
> 2008 07 34 2 127
> 2008 08 33 0 118
> 2008 09 1 0 6
> 2008 10 0 0 1
> 2007 11 47 4 107
> 2007 12 35 2 74
>
> SQL Statement:
>
> SELECT Year([quoteDate]) AS [Year], Nz(Format([quoteDate],"mm"),0) AS
> [Month], Nz(Sum(IIf([quoteStatus]=1,1,0)),0) AS Awarded,
> Nz(Sum(IIf([quoteStatus]=2,1,0)),0) AS Lost, Count(Nz([quoteID],0)) AS [Total
> Quotes]
> FROM tblQuoteHeader
> GROUP BY Year([quoteDate]), Nz(Format([quoteDate],"mm"),0)
> ORDER BY Year([quoteDate]) DESC , Nz(Format([quoteDate],"mm"),0);
>
>
> I'd like to lay this out in a crosstab format so that my months run across
> the top as column headers but Access won't allow more than one "value" for a
> crosstab.
>
> Is there a way for me to accomplish this outside of a crosstab or have I
> written this query incorrectly that prevents me from using crosstabs?
>
> Thanks for your help in advance.
>
> Alan