Query based on multiple criteria

[bd528]

I have the following query :-

SELECT tblQuotes.Region, Count(tblQuotes.Region) AS xCount, tblQuotes.Sales_Route
FROM tblQuotes
GROUP BY tblQuotes.Region, tblQuotes.Quote_Status, tblQuotes.Sales_Route
HAVING (((Count(tblQuotes.Region))<>0) AND ((tblQuotes.Quote_Status)="Accepted"));

This outputs my results grouped by Sales_Route, but by row. There are only 2 Sales_Route available - "TPI" and "Sales Mailbox".

What I would like is the query to output the following columns (grouped by region) :-

Region // Count of Sales_Route = "TPI" // Count of Sales_Route = "Sales Mailbox"

Is this possible?

 

[Ranman256]

use the query builder and create a CROSSTAB query.
it will ask what you want as rows,
what to use as columns
and what to count.

 

[bd528]

use the query builder and create a CROSSTAB query.
it will ask what you want as rows,
what to use as columns
and what to count.

Perfect - thank you. The SQL I ended with was :-

TRANSFORM Count(tblQuotes.Sales_Route) AS CountOfSales_Route
SELECT tblQuotes.Region
FROM tblQuotes
WHERE (((tblQuotes.Sales_Route)<>"0"))
GROUP BY tblQuotes.Region
PIVOT tblQuotes.Sales_Route;

 

[The_Doc_Man]

Just for the record, sometimes the wizards are dumber than a box of rocks. However, for cases like this where a specific wizard exists to do what you needed to do (build a crosstab query in this case), they work very will. Keep your wizards in your list of useful things to use in Access.