Catch-all column in crosstab

[Rajat]

Hi,

I have 5 discrete data values in a column that I am using
as a column heading in my crosstab query.

However, I am interested in showing just one of the values
in its own column and the sum of the other 4 values in a
second catch-all column.

For example, if the value of interest is "Emergency", I
want to show the sum of the other 4 discrete values in a
column called "Non-Emergency"

I know I can hardcode the "Emergency", in the query
properties, but how do I show the non-EMergency column in
the query?

Thanks!

Regards,
Rajat

 

[Dejan Mladenovic]

One solution is to have two queries. The first one,
Query1 is just the cross tab, the way wizard creates it,
with all 5 headings. If your headings are known in
advance, then it is safe to build Query2, based on
Query1. Show the desired field and calculate whatever you
want with the others.

Assume your discrete values are
{"Emergency","F2","F3","F4","F5"}
Then Queary1, a cross tab query, will returns fields
Emergency,F2,F3,F4,F5

Query2 may look like this:
SELECT Emergency, F2+F3+F4+F5 AS NonEmergency
FROM Query1