Group Sums (1 Viewer)

[gfultz]

Hi All,

I have a query that merges data from two sources and creates a spread of data for use in a report. This all functions and the report works great.

What I want to do now is add subtotals to the report. However, when I go down to the second grouping, I need to sum a value only once. Here is a summary of the grouping/sorting


Project
>Category
>>Sub Category
>>>Sys1Data
>>>>Detail section (Sys2Data)

Right now I am subtotaling fine for the Sys2Data in the Sys1Data section footer. What I need to do is subtotal the Sys1Data at the category level. However, each record in the query contains the Sys1Data repeated for each unique record in Sys2Data. So the subtotal sums all of it, even the duplicates.

Any ideas on how to sum the data in the header section of the Sys1Data grouping only?

Garrett

 

[PNGBill]

Can you divide the result by 2 and get a correct value?

 

[gfultz]

No, it is not correct if divided by 2. This is because there might be 0 sys2data results, or 15.

What I ended up doing was creating a global variable and summing to the variable in the on format of the header section of sysdata1, resetting the variable at the higher level where I want to restart it. It works flawlessly.

Thanks.