Adding Distinct Subtotals in a Matrix

[tpsmith]

Hi

I am trying to add together the subtotals for a matrix but in the dataset the value appears for each line and I need to be able to drill down into the detail of the report so cannot just summarise in the query .... i hope this makes sense, I struggle to explain whats needed so here is a simple breakdown.

Department Course Target Student Code
ART ABC 12 AS1234
ART ABC 12 AS3455
ART ABC 12 AS3453
ART ABC 12 AS4567
ART ABC 12 AS4242
ART DEF 6 AS2223
ART DEF 6 AS1111
ART DEF 6 AS4444

I need to be able to group by the department and give the total of 18 for the department total so not summing the target field on the report. I can group by the department and the course and display the target for each course but when I go to sum the target for the department i get the value for each student not the coure.

Please help!!

Thank you
Tony

 

[xlbo]

I'd be handling this in the query - simply divide the target by count of course - then it is a simple sum. You can do this in the report as well but generallyI try to do these kind of things in the query

Alternatively you can use ROWNUMBER in your query and only populate the target where ROWNUMBER = 1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tpsmith]

Thank Geoff, rownumber may be the way forward as I have detailed data in the query and not summarised.

Regards
Tony