Crosstab Query Adding Pivoted Columns

[Mysterydave]

Hi,

If I have a cross tab query which counts an [id number] and pivots on a [status] for a [course], how do I add a new row heading that will sum only a certain few status's. E.g. The [status] field has "A", "B", "C", "D". I want to add a row heading that is the sum of [id numbers] under "A" and "D"?

Thanks

[Stewart Ross]

Hi. If you want to show only the "A" and "D" columns in your crosstab list you need to restrict the rows to those where the status is in your 'required list' of "A" and "D". To do so in a crosstab query, follow these steps:

1. add another copy of your Status field into the query editor grid
2. change the default type in the Total row for that field from Group By to Where
3. in the Criteria row for that field add In ("A", "D")
4. save the query and run it

If you really mean that you want to keep the crosstab headers for A, B, C and D as they are and show a separate row heading which is the total for A and D only then forget the WHERE clause - you won't need it. Instead, add a calculated field as a row heading containing an expression such as the following:

AD Total: Sum(IIF([status] In ("A", "D"), 1, 0))

Make sure you change the Group By status for this field to Expression as otherwise you will receive an error telling you that you can't use an aggregate function in a group by.

-Stewart

[Mysterydave]

Brilliant. The second one was what I was after. I had started to create new select queries with the original cross tab. I made an expression in there to add up the fields I needed as an expression. This reduces my ever growing mountain of queries I'm amassing, and is much cleaner.

Cheers

[Mysterydave]

Could you explain how this works:

AD Total: Sum(IIF([status] In ("A", "D"), 1, 0))

Does it count 1 for every occurance of A or D, is that right?

[Mysterydave]

This is now my working query:

TRANSFORM CLng(Nz(Count([DIR__ID]),0)) AS N
SELECT   [Direct_Applications_2009 Query].DIR_AOS_CODE, 
         [Direct_Applications_2009 Query].DIR_AOS_DESC, 
         [Direct_Applications_2009 Query].UG_PG, 
         [Direct_Applications_2009 Query].FULL_PART, 
         Count([Direct_Applications_2009 Query].DIR__ID) AS [Total Of DIR__ID], 
         Sum(IIf([DIR_STAGE_CODE] In ("DIRCD","DIRCF","DIRCO","DIRUD","DIRUF","DIRUO"),1,0)) AS Offers
FROM     [Direct_Applications_2009 Query]
GROUP BY [Direct_Applications_2009 Query].DIR_AOS_CODE, 
         [Direct_Applications_2009 Query].DIR_AOS_DESC, 
         [Direct_Applications_2009 Query].UG_PG, 
         [Direct_Applications_2009 Query].FULL_PART
ORDER BY [Direct_Applications_2009 Query].UG_PG DESC 
PIVOT    [Direct_Applications_2009 Query].DIR_STAGE_CODE In
         ("APP","DIRUF","DIRUO","DIRCF","DIRCO","DIRUD","DIRCD","DIRCA","DIRRJ","DIRW","DIRDEF");

Thankyou again

[Stewart Ross]

@Mysterydave
That is correct; the IIF simply tests to see if the [status] field is an "A" or a "D", returns 1 if it is and 0 if it isn't. Sum then totals the value returned - which will be the same as a count of the number of rows that contain an A or a D status at the level of the grouping involved in the crosstab.

Glad you have adapted it to work for your particular query!

-Stewart