如何在访问中创建基于另一个表的条件和的计算列

Question

在MS ACCESS 2016中，是否可以在一个表中创建一个列，该列是另一个表的条件求和？

示例

表1-列

ID, NAME, TOTAL

表2-列

ID, NAME, IDREF, CUSTO

数据：

表1

ID | Name  | Total
---+-------+----------------------------------------------------------------
35 |  Test |  "SUM(CUSTO) of ALL ELEMENTS OF TABLE 2 WHERE table2.IDREF = table1.ID"

表2

ID | Name  | IDREF | CUSTO
---+-------+-------+--------
1  | Test  |  35   |   50
2  | Test  |  35   |   30
3  | abcd  |  12   |   30
4  | Test  |  35   |   10

结果应该是：

表1

ID | Name | Total
---+------+------
35 | Test |  90      (50 + 30 + 10 from table 2 where idref = 35)

Answer 1

您可以使用子查询：

select t1.*,
(select sum(t2.CUSTO)
from table2 as t2
where t2.idref = t1.id
) as total
from table1 as t1;

Answer 2

更有效地，考虑JOIN中的聚合子查询只运行一次，而不是SELECT子查询，该子查询在每行运行一次。

SELECT t1.*, agg.Total
FROM table1 as t1
INNER JOIN
( SELECT t2.idref, SUM(t2.CUSTO) AS Total
FROM table2 as t2
GROUP BY t2.idref
) AS agg
ON agg.idref = t1.id

或者，根据Allen Browne在MS Access：中的优化查询提示，将子查询替换为精确保存的查询，以提高效率

子查询将比域聚合函数快得多。在大多数情况下，堆叠查询将更快(即您在此查询中作为"表"包含的另一个已保存查询(

SELECT t1.*, q.Total
FROM table1 as t1
INNER JOIN mySavedAggQuery q
ON q.idref = t1.id