DSum Help (1 Viewer)

[shawnacker]

I am trying to create a query which will be used to create a pareto chart. I have my first criteria which is my number of differences and I have calculated a percentage based upon each type of difference. I would like to create an ongoing sum of the differences until it finally reaches 100%.

Among other criteria used to determine the time period, I have a column for NUMBER OF DIFFERENCES, which totals the number and groups based upon each type of difference.

I have a column for TOTAL ENTRIES which is used for my percentage calculation.

I have a column for PERCENT OF DIFFERENCES, which takes number and divides it by total to get a percentage.

From here I would simply like to create a cumulative sum. I have read in numerous places that is best to do this in a report but this is for a chart and based upon a query so I can have a different chart whenever desired. I have tried many different types of DSum calculations but I have only received error messages. Could anyone please help me with the proper formula?

Thanks in advance,

Shawn

 

[Pat Hartman]

It is NOT possible to create a running sum in a query unless each row of the table has a unique identifier AND that unique identifier provides the controlling sequence for the running sum. If your case satisfies BOTH criteria, you can use DSum() with a criteria statement something like - "YourField <= Table.YourField". You should be aware that this is extremely slow if you have more than a few hundred rows since for each row of the recordset the DSum() essentially needs to re-read all the previous rows to sum them.

A more efficient method is to read the recordset (sorted in what ever sequence you need) in code, calculate the running sum and write the results to a temp table. Then use the temp table as the recordsource for the graphs.