Get crosstab query to group AND count

[connie]

Hi!

I have a crosstab query that does the following:

Dept_Name (Row Heading) -> Group By
Phase (Row Heading) -> Group By
Status (Column Heading) -> Group By
Description (Value) -> Count

What it's doing is counting every description under the Phase and Dept I've specified, etc. But I want it to instead count the Description by group instead of by total. For example, if I had 5 apples and 4 bananas under description, I want it to count this as 2 (since there are 2 types of descriptions) instead of as 9.

This should be simple but I'm stumped!! Thank you

 

[ByteMyzer]

To count the distinct instances of the Description by these groupings, an effective method is to execute a Transform query against an in-line subquery. You might try the following SQL syntax (substitute highlighted text with actual table/field names):

TRANSFORM Count(T1.[B][I]Description[/I][/B])
SELECT T1.[B][I]Dept_Name[/I][/B], T1.[B][I]Phase[/I][/B]
FROM (
       SELECT DISTINCT T1.[B][I]Dept_Name[/I][/B], T1.[B][I]Phase[/I][/B],
                       T1.[B][I]Status[/I][/B], T1.[B][I]Description[/I][/B]
       FROM [B][I]MyTable[/I][/B] AS T1
     ) AS T1
GROUP BY T1.[B][I]Dept_Name[/I][/B], T1.[B][I]Phase[/I][/B]
PIVOT T1.[B][I]Status[/I][/B];

 

[connie]

To count the distinct instances of the Description by these groupings, an effective method is to execute a Transform query against an in-line subquery. You might try the following SQL syntax (substitute highlighted text with actual table/field names):

TRANSFORM Count(T1.[B][I]Description[/I][/B])
SELECT T1.[B][I]Dept_Name[/I][/B], T1.[B][I]Phase[/I][/B]
FROM (
       SELECT DISTINCT T1.[B][I]Dept_Name[/I][/B], T1.[B][I]Phase[/I][/B],
                       T1.[B][I]Status[/I][/B], T1.[B][I]Description[/I][/B]
       FROM [B][I]MyTable[/I][/B] AS T1
     ) AS T1
GROUP BY T1.[B][I]Dept_Name[/I][/B], T1.[B][I]Phase[/I][/B]
PIVOT T1.[B][I]Status[/I][/B];

Wow...that's really technical. What do you mean "against an in-line subquery?"

I was thinking about this over lunch and was going to try just basing the crosstab query off of another query that groups all of those categories (Dept_Name, Phase, Status, Description). I think it would count correctly then?

 

[ByteMyzer]

Wow...that's really technical. What do you mean "against an in-line subquery?"

I was thinking about this over lunch and was going to try just basing the crosstab query off of another query that groups all of those categories (Dept_Name, Phase, Status, Description). I think it would count correctly then?

How about actually TRYING my solution to see if it works for you, instead of second-guessing it first?

 

[connie]

How about actually TRYING my solution to see if it works for you, instead of second-guessing it first?

Because, as I said, I don't understand what you meant by "against an in-line subquery." I wasn't second-guessing whether or not it would work; I didn't fully understand it. I was also in the process of saving the grouping query I thought of over lunch when I received the notification that you'd updated the thread w/the Transform suggestion.

 

[ByteMyzer]

An Inline Subquery is a Subquery that may be used in a main Query in place of a table or reference to another query.

For example:
Query1:

SELECT * FROM Table1 WHERE Field1 = '1';

Query2:

SELECT * FROM Query1 WHERE Field2 = 'X';

Instead of using two queries, you can use a Query-Inline Subquery structure like the following:

SELECT * FROM
   (SELECT * FROM Table1 WHERE Field1 = '1') AS Query1
WHERE Field2 = 'X';

 

[connie]

Thank you!