Getting the count of various rows in one query.

[Ach]

I've been toying with this for a few hours but I always end up with the wrong result. Hopefully an expert here can provide some insight. I have a table called colors with two columns PNAME and COLOR. There are various products and only three possible colors. E.g:

Tablename: COLORS

PNAME COLOR
-----------------------
tshirt blue
pants blue
jacket blue
jacket green
shoes green
vest black


Now what I'm trying to do is create a single query that would provide me with the count of colors in three different columns.
E.g

Blue Green Black
-----------------------
3 2 1

I can get these results easily by creating several queries and then getting the results into one query from these, but I'm trying to learn how to put together nested queries. Any help would be greatly appreciated.

 

[CraigDolphin]

Well, you could make a crosstab query that'd do it:

something like

TRANSFORM Count(Colors.COLOR) AS CountOfCOLOR
SELECT "Count" AS TheColor
FROM Colors
GROUP BY "Count"
PIVOT Colors.COLOR;

 

[Ach]

Awesome. Now I have to read up on crosstab queries.
There is one quetion i forgot to mention. I have two products int he above table with null or empty values. How would i be able to count these values also with this method. At the moment I get zero, but I have two products that statisfy these conditions.

thanks for the help.

 

[Ach]

Nevermind, I was able to work it out.

thanks again.

 

[CraigDolphin]

Ok, try this then:

TRANSFORM Count(Colors.PNAME) AS CountOfPNAME
SELECT "Count" AS TheColor
FROM Colors
GROUP BY "Count"
PIVOT Colors.COLOR;
[CODE]

 

[CraigDolphin]

Oops...ok...glad you got it sorted

 

[Ach]

Is it possible to do the same without crosstab queries, by using normal queries?

 

[CraigDolphin]

I'm not 100% sure but I think that you must use a crosstab query if you want to convert rows to columns in access. It would be easy enough to create the list of data in one query but there would be one color per row, not one color per column like you specified.

 

[Ach]

I was able to get the same results with this but without any row headings like the crosstab generates. Was wondering if this method is generally accepted as good query design or any other thoughts anyone has.

SELECT  *

FROM

(SELECT Count(color) AS BLUE
FROM COLORS
WHERE Color="blue") AS BLUE
,

(SELECT Count(color) AS Green
FROM COLORS
WHERE Color="green") AS Green
,

(SELECT Count(color) AS Black
FROM COLORS
WHERE Coverage="black") AS Black


;

 

[CraigDolphin]

The problem/good thing with that is that you need to know in advance all the possible values that you want listed so you can setup the relevent subquery to return results for that value. Thus, if you start carrying a new color, you need to go back and redesign your query to also count that color. Using a crosstab, there's no need to redesign your query every time a new color option is added.

On the other hand, if all you ever want is to count certain colors, then your approach might be superior.

 

[CraigDolphin]

I should mention also that you could use domain agregate functions (eg DCount) to count records for particular colors also (perhaps in the control source for unbound textboxes on a form) and avoid the need for a query altogether.

 

[Ach]

The problem/good thing with that is that you need to know in advance all the possible values that you want listed so you can setup the relevent subquery to return results for that value. Thus, if you start carrying a new color, you need to go back and redesign your query to also count that color. Using a crosstab, there's no need to redesign your query every time a new color option is added.

On the other hand, if all you ever want is to count certain colors, then your approach might be superior.

I see your point regarding the crosstab advantages. Now that I'm familiar with two ways of doing this I can choose the best one regarding the situation. You've been tremendous help.

 

[CraigDolphin]

Happy to help