Sum of Types in Group

[hughess7]

Hi all

I have a report that lists all Activities and totals for months, based on
crosstab query. It is grouped by country and specialist. Is there a way of
getting the totals to add up per country on the actual type of activities
rather than just the Sum of the all activities which is meaningless.

report looks like:

UK
Fred Bloggs Jan Feb Mar etc
Audit 1 4 5
Holiday 6

Jane Doe Jan Feb Mar etc
Audit 4 2 3
Sick 5

Want totals to show UK 5 Audits for Jan, 6 Audits for Feb etc

Tried sum(iif([Activity]="Audit",1,0)) and abs(sum(activity="Audit") but
these just display 2 for this example. I guess it is saying there are two
rows so its more of a count?

Is there a way I can do this?

Thanks in advance for any help.
Sue

 

[KARL DEWEY]

Yes, use the crosstab query you now have and copy. Edit in query design view
to remove individuals.