Summing in Cross Tab Query

[Ciaran]

I want to be able to total each of the rows and each of the columns in the attached cross tab query.

The query currently shows the total number of bugs per functional area. The bugs can be either 1,2 or 3 depending on their severity.

I want to be able to total each of the 3 levels separately (across all areas) and also to total the number of bugs per area.

ie i want another column down the right showing the total number of level 1, 2 and 3 bugs for each area - eg ACS has 14 bugs total

I also want a row underneath showing the total number of level 1, level 2 and level 3 bugs. eg level 1 has 37 bugs.

Thanks for any help

Ciaran

 

[Tiro Cupidus]

I'll be watching this one =) I've wanted the same thing, but had to use a report instead.

 

[Ciaran]

Attachment

 

[Pat Hartman]

If you use the crosstab wizard to build the query, it automatically inserts a totals column. To total the rows, you'll need to use a report. Queries cannot return rows of differing cardinality. ie some detail rows and a summary row. You could create a query that sums the crosstab query and union that with the original query but you'll have trouble ordering the recordset. The total could very well end up in the middle of the recordset unless you have something you can sort on to make a predictable order.

 

[Tiro Cupidus]

Ah, I remembered why I couldn't use a crosstab (something I discovered after I had made the report anyway). I needed subcolumns.