我怎样才能运行总计一列添加到Access查询？ [英] How can I add a running total of one column to an Access query?

问题描述

我有一个包含在一个领域总销售额​​在过去12个月对应于特定产品的百分比的查询。例如：

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:

产品1 - 38％
产品2 - 25％
产品3 - 16％
（...）

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

中的记录进行排序由百分比列降序排列，并且该总和必须是100％。我想创建一个新的列，增加了previous百分比运行总计，像这样的：

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:

产品1 - 38％ - 38％
产品2 - 25％ - 63％
产品3 - 16％， - 79％
（...，直到达到最后的产物和100％的小计）

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

我怎么能这样做呢？

推荐答案

如果你有一个 ID 字段或日期字段，你可以使用这个相关性变化子查询。

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;

有人谁是更好的方式在SQL不是我，但是如果这可以帮助或者向你的答案那也不错。

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

这篇关于我怎样才能运行总计一列添加到Access查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！