Sorting Crosstab Query

M

Mark Jackson

Hello,

I need to sort a crosstab query by year. I have 2006 data that I don't want
to see, but would like to keep. Any help is appreciated. Here is the SQL

TRANSFORM Count(Quotes.SolicitationID) AS CountOfSolicitationID
SELECT Quotes.[Won/Lost], Format([Type],"mmm") AS SortType,
Format([SubmitDate],"mmyyyy") AS SortDate, Quotes.AwardedTo,
Count(Quotes.SolicitationID) AS [Total Of SolicitationID],
Sum(([Quantity]*[QuotedPrice])) AS TotalValue
FROM Quotes
GROUP BY Quotes.[Won/Lost], Format([Type],"mmm"),
Format([SubmitDate],"mmyyyy"), Quotes.AwardedTo
PIVOT Format([SubmitDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks
Mark
 
K

KARL DEWEY

Try this --
TRANSFORM Count(Quotes.SolicitationID) AS CountOfSolicitationID
SELECT Quotes.[Won/Lost], Format([Type],"mmm") AS SortType,
Format([SubmitDate],"mmyyyy") AS SortDate, Quotes.AwardedTo,
Count(Quotes.SolicitationID) AS [Total Of SolicitationID],
Sum(([Quantity]*[QuotedPrice])) AS TotalValue
FROM Quotes
WHERE (((Format([SubmitDate],"yyyy"))<>2006))
GROUP BY Quotes.[Won/Lost], Format([Type],"mmm"),
Format([SubmitDate],"mmyyyy"), Quotes.AwardedTo
PIVOT Format([SubmitDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Mark Jackson said:
Hello,

I need to sort a crosstab query by year. I have 2006 data that I don't want
to see, but would like to keep. Any help is appreciated. Here is the SQL

TRANSFORM Count(Quotes.SolicitationID) AS CountOfSolicitationID
SELECT Quotes.[Won/Lost], Format([Type],"mmm") AS SortType,
Format([SubmitDate],"mmyyyy") AS SortDate, Quotes.AwardedTo,
Count(Quotes.SolicitationID) AS [Total Of SolicitationID],
Sum(([Quantity]*[QuotedPrice])) AS TotalValue
FROM Quotes
GROUP BY Quotes.[Won/Lost], Format([Type],"mmm"),
Format([SubmitDate],"mmyyyy"), Quotes.AwardedTo
PIVOT Format([SubmitDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks
Mark
Click to expand...
 
M

Mark Jackson

Karl,

Thank you very much. It works perfect.

Mark

KARL DEWEY said:
Try this --
TRANSFORM Count(Quotes.SolicitationID) AS CountOfSolicitationID
SELECT Quotes.[Won/Lost], Format([Type],"mmm") AS SortType,
Format([SubmitDate],"mmyyyy") AS SortDate, Quotes.AwardedTo,
Count(Quotes.SolicitationID) AS [Total Of SolicitationID],
Sum(([Quantity]*[QuotedPrice])) AS TotalValue
FROM Quotes
WHERE (((Format([SubmitDate],"yyyy"))<>2006))
GROUP BY Quotes.[Won/Lost], Format([Type],"mmm"),
Format([SubmitDate],"mmyyyy"), Quotes.AwardedTo
PIVOT Format([SubmitDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Mark Jackson said:
Hello,

I need to sort a crosstab query by year. I have 2006 data that I don't want
to see, but would like to keep. Any help is appreciated. Here is the SQL

TRANSFORM Count(Quotes.SolicitationID) AS CountOfSolicitationID
SELECT Quotes.[Won/Lost], Format([Type],"mmm") AS SortType,
Format([SubmitDate],"mmyyyy") AS SortDate, Quotes.AwardedTo,
Count(Quotes.SolicitationID) AS [Total Of SolicitationID],
Sum(([Quantity]*[QuotedPrice])) AS TotalValue
FROM Quotes
GROUP BY Quotes.[Won/Lost], Format([Type],"mmm"),
Format([SubmitDate],"mmyyyy"), Quotes.AwardedTo
PIVOT Format([SubmitDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks
Mark
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

Group by Range in Crosstab Query 5
UNION and Crosstab query 5
Crosstab returns blank fields 6
Date Sorting in Crosstab 3
Custom Format for crosstab headings 2
Crosstab qry not adding up 1
Crosstab Returns Empty Fields 2
Crosstab - Multiple values on each row 5

Top