CODEWITHSUNDEEP
sqlms-accessvbams-access-2007
user3614001
user3614001

Reputation: 13

How can I add a running total of one column to an Access query?

I have a query that contains in one field the percentage of total sales corresponding to a specific product in the past 12 months. For example:

Product 1 - 38%
Product 2 - 25%
Product 3 - 16%
(...)

The records are sorted in descending order by the percentage column, and the sum of that has to be 100%. I want to create a new column that adds the previous percentages as a running total, like this:

Product 1 - 38% - 38%
Product 2 - 25% - 63%
Product 3 - 16% - 79%
(... until it reaches the last product and a 100% sub-total)

How could I do this?

Upvotes: 0

Views: 777

Answers (1)

Mark C.
Mark C.

Reputation: 6450

If you have an ID field, or a date field, you can use a variation of this correlated subquery.

SELECT t.*,
     t.productpct+[prev_value] AS RunningSum,

 (select sum([ProductPct])            
 from test AS t2                   
  WHERE             
  t2.ID < t.ID  
 ) AS Prev_Value

FROM test AS t;

There are people who are way better at SQL than I, however if this helps or gives you your answer then great.

Upvotes: 1

Related Questions