crosstab report layout?

A

Alan

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
 
K

KARL DEWEY

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 said:
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
Click to expand...
 
A

Alan

Thanks Karl!

That worked well.

Alan



KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report Limitation 2
Dynamic Crosstab 3
Dynamic Crosstab - Repost 5
How to remove the prompt criteria from the report ? 4
DATABASE REPORT/CROSSTAB QUERY HELP 2
Crosstab Query 2
Crosstab report with totals in the footer 2
very slow report with dcount() 6

Top