时间戳

首页 > 解决方案 > 查询中突然错误的运行总和值

ms-access - 查询中突然错误的运行总和值

问题描述

我正在使用相关子查询来生成名为 SumDemerits 的运行总和，以便检查数据输入错误。查询给出以下内容，其中包括原始表的副本：

    DemeritID   ID  DemeritDate Amount  DemeritType SumDemerits

83  42  5/23/2019   10  1   10
112 42  5/24/2019   -10 2   0
88  42  7/1/2019    2   1   2
89  42  7/9/2019    -2  1   0
90  42  7/10/2019   6   1   6
91  42  7/11/2019   3   1   9
92  42  7/12/2019   -2  1   7
97  42  7/13/2019   1   1   8
98  42  7/14/2019   2   1   10
99  42  7/15/2019   -10 2   0
120 42  8/8/2019    2   1   2
121 42  8/9/2019    1   1   3
122 42  8/10/2019   1   1   4
123 42  8/11/2019   1   1   5
125 42  8/12/2019   -2  1   3
126 42  8/13/2019   1   1   4
127 42  8/14/2019   -2  1   2
128 42  8/15/2019   -2  1   0
129 42  8/17/2019   0   1   0
130 42  8/18/2019   0   1   0
131 42  8/19/2019   4   1   4
132 42  8/20/2019   -2  1   2
133 42  8/21/2019   3   1   5
134 42  8/22/2019   2   1   7
135 42  8/23/2019   2   1   9
136 42  8/24/2019   -2  1   7
137 42  8/25/2019   1   1   8
138 42  8/26/2019   -2  1   6
139 42  8/27/2019   2   1   8
140 42  8/28/2019   1   1   9
141 42  8/29/2019   -2  1   7
142 42  8/30/2019   -2  1   13
146 42  8/31/2019   -2  1   9
147 42  9/1/2019    2   1   11
150 42  9/2/2019    2   1   15
151 42  9/3/2019    -2  1   5
152 42  9/4/2019    -2  1   3
148 42  9/5/2019    -2  1   1
154 42  9/6/2019    10  1   11
165 42  9/7/2019    -11 2   0
158 42  10/3/2019   3   1   3
159 42  10/4/2019   -2  1   1
160 42  10/5/2019   6   1   16
164 42  10/7/2019   3   1   19
171 42  10/8/2019   10  2   48
162 42  10/8/2019   10  2   48
166 42  11/14/2019  0   1   48
167 42  11/15/2019  1   1   49
168 42  11/18/2019  3   1   52
169 42  11/19/2019  6   1   58
170 42  11/20/2019  30  2   88

运行总 SumDemerits 按预期工作，直到 2019 年 8 月 30 日突然给出 13 而不是 5。这是我的查询的 sql，它只是复制原始表并添加 SumDemerits 列。

SELECT Demerits.DemeritID, Demerits.ID, Demerits.DemeritDate, Demerits.Amount, Demerits.DemeritType, (SELECT Sum(dupe.[Amount]) FROM Demerits as dupe WHERE dupe.DemeritDate <= [Demerits].[DemeritDate]) AS SumDemerits
FROM Demerits
WHERE (((Demerits.ID)=[ID]))
ORDER BY Demerits.DemeritDate;

生成可以剪切和粘贴的表格版本的 html 代码：

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg .tg-0pky{border-color:inherit;text-align:left;vertical-align:top}
</style>
<table class="tg">
  <tr>
    <th class="tg-0pky">DemeritID</th>
    <th class="tg-0pky">BoyID</th>
    <th class="tg-0pky">DemeritDate</th>
    <th class="tg-0pky">Amount</th>
    <th class="tg-0pky">DemeritType</th>
    <th class="tg-0pky">SumDemerits</th>
  </tr>
  <tr>
    <td class="tg-0pky">83</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">5/23/2019 0:00</td>
    <td class="tg-0pky">10</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">10</td>
  </tr>
  <tr>
    <td class="tg-0pky">112</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">5/24/2019 0:00</td>
    <td class="tg-0pky">-10</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">0</td>
  </tr>
  <tr>
    <td class="tg-0pky">88</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">7/1/2019 0:00</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">2</td>
  </tr>
  <tr>
    <td class="tg-0pky">89</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">7/9/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">0</td>
  </tr>
  <tr>
    <td class="tg-0pky">90</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">7/10/2019 0:00</td>
    <td class="tg-0pky">6</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">6</td>
  </tr>
  <tr>
    <td class="tg-0pky">91</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">7/11/2019 0:00</td>
    <td class="tg-0pky">3</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">9</td>
  </tr>
  <tr>
    <td class="tg-0pky">92</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">7/12/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">7</td>
  </tr>
  <tr>
    <td class="tg-0pky">97</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">7/13/2019 0:00</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">8</td>
  </tr>
  <tr>
    <td class="tg-0pky">98</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">7/14/2019 0:00</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">10</td>
  </tr>
  <tr>
    <td class="tg-0pky">99</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">7/15/2019 0:00</td>
    <td class="tg-0pky">-10</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">0</td>
  </tr>
  <tr>
    <td class="tg-0pky">120</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/8/2019 0:00</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">2</td>
  </tr>
  <tr>
    <td class="tg-0pky">121</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/9/2019 0:00</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">3</td>
  </tr>
  <tr>
    <td class="tg-0pky">122</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/10/2019 0:00</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">4</td>
  </tr>
  <tr>
    <td class="tg-0pky">123</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/11/2019 0:00</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">5</td>
  </tr>
  <tr>
    <td class="tg-0pky">125</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/12/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">3</td>
  </tr>
  <tr>
    <td class="tg-0pky">126</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/13/2019 0:00</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">4</td>
  </tr>
  <tr>
    <td class="tg-0pky">127</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/14/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">2</td>
  </tr>
  <tr>
    <td class="tg-0pky">128</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/15/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">0</td>
  </tr>
  <tr>
    <td class="tg-0pky">129</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/17/2019 0:00</td>
    <td class="tg-0pky">0</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">0</td>
  </tr>
  <tr>
    <td class="tg-0pky">130</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/18/2019 0:00</td>
    <td class="tg-0pky">0</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">0</td>
  </tr>
  <tr>
    <td class="tg-0pky">131</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/19/2019 0:00</td>
    <td class="tg-0pky">4</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">4</td>
  </tr>
  <tr>
    <td class="tg-0pky">132</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/20/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">2</td>
  </tr>
  <tr>
    <td class="tg-0pky">133</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/21/2019 0:00</td>
    <td class="tg-0pky">3</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">5</td>
  </tr>
  <tr>
    <td class="tg-0pky">134</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/22/2019 0:00</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">7</td>
  </tr>
  <tr>
    <td class="tg-0pky">135</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/23/2019 0:00</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">9</td>
  </tr>
  <tr>
    <td class="tg-0pky">136</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/24/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">7</td>
  </tr>
  <tr>
    <td class="tg-0pky">137</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/25/2019 0:00</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">8</td>
  </tr>
  <tr>
    <td class="tg-0pky">138</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/26/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">6</td>
  </tr>
  <tr>
    <td class="tg-0pky">139</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/27/2019 0:00</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">8</td>
  </tr>
  <tr>
    <td class="tg-0pky">140</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/28/2019 0:00</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">9</td>
  </tr>
  <tr>
    <td class="tg-0pky">141</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/29/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">7</td>
  </tr>
  <tr>
    <td class="tg-0pky">142</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/30/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">13</td>
  </tr>
  <tr>
    <td class="tg-0pky">146</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">8/31/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">9</td>
  </tr>
  <tr>
    <td class="tg-0pky">147</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">9/1/2019 0:00</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">11</td>
  </tr>
  <tr>
    <td class="tg-0pky">150</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">9/2/2019 0:00</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">15</td>
  </tr>
  <tr>
    <td class="tg-0pky">151</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">9/3/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">5</td>
  </tr>
  <tr>
    <td class="tg-0pky">152</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">9/4/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">3</td>
  </tr>
  <tr>
    <td class="tg-0pky">148</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">9/5/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">1</td>
  </tr>
  <tr>
    <td class="tg-0pky">154</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">9/6/2019 0:00</td>
    <td class="tg-0pky">10</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">11</td>
  </tr>
  <tr>
    <td class="tg-0pky">165</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">9/7/2019 0:00</td>
    <td class="tg-0pky">-11</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">0</td>
  </tr>
  <tr>
    <td class="tg-0pky">158</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">10/3/2019 0:00</td>
    <td class="tg-0pky">3</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">3</td>
  </tr>
  <tr>
    <td class="tg-0pky">159</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">10/4/2019 0:00</td>
    <td class="tg-0pky">-2</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">1</td>
  </tr>
  <tr>
    <td class="tg-0pky">160</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">10/5/2019 0:00</td>
    <td class="tg-0pky">6</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">16</td>
  </tr>
  <tr>
    <td class="tg-0pky">164</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">10/7/2019 0:00</td>
    <td class="tg-0pky">3</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">19</td>
  </tr>
  <tr>
    <td class="tg-0pky">171</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">10/8/2019 0:00</td>
    <td class="tg-0pky">10</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">48</td>
  </tr>
  <tr>
    <td class="tg-0pky">162</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">10/8/2019 0:00</td>
    <td class="tg-0pky">10</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">48</td>
  </tr>
  <tr>
    <td class="tg-0pky">166</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">11/14/2019 0:00</td>
    <td class="tg-0pky">0</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">48</td>
  </tr>
  <tr>
    <td class="tg-0pky">167</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">11/15/2019 0:00</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">49</td>
  </tr>
  <tr>
    <td class="tg-0pky">168</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">11/18/2019 0:00</td>
    <td class="tg-0pky">3</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">52</td>
  </tr>
  <tr>
    <td class="tg-0pky">169</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">11/19/2019 0:00</td>
    <td class="tg-0pky">6</td>
    <td class="tg-0pky">1</td>
    <td class="tg-0pky">58</td>
  </tr>
  <tr>
    <td class="tg-0pky">170</td>
    <td class="tg-0pky">42</td>
    <td class="tg-0pky">11/20/2019 0:00</td>
    <td class="tg-0pky">30</td>
    <td class="tg-0pky">2</td>
    <td class="tg-0pky">88</td>
  </tr>
</table>

标签： ms-accesscorrelated-subquery

解决方案

出现问题是因为原始查询有一个参数 ([ID]): WHERE (((Demerits.ID)=[ID])) 相关子查询还需要限制它按 ID 计数的行，否则它会计算其他的缺点如果它们在日期范围内，则为 ID。所以将子查询更改为： (SELECT Sum(dupe.[Amount]) FROM Demerits as dupe WHERE dupe.DemeritDate <= [Demerits].[DemeritDate] AND [ID] = [ID])

推荐阅读