我试图通过使用查询在 Access 中创建计算。目前,一个查询计算“MPP Oil”(最大生产潜力)的值,另一个查询需要使用该值来计算“未分配损失”。这些计算使用来自基本查询“PEBaseQuery”的公司/资产/年份数据。计算未分配损失的其他输入值是使用 ID 引用的...但我的代码似乎有问题,请帮忙!
SELECT
qb1.CompanyName,
qb1.AssetName,
qb1.Year,
(qb3.MPPOilRevised
- SUM(qb1.DatapointValue)
- SUM(qb2.DatapointValue * 1000000)) AS Result
FROM
((PEBaseQuery AS qb1
INNER JOIN PEBaseQuery AS qb2
ON qb1.Year = qb2.Year AND qb1.AssetName=qb2.AssetName)
INNER JOIN PE_MPPOilRevised AS qb3
ON qb1.Year = qb3.Year AND qb1.AssetName=qb3.AssetName)
WHERE
qb1.DatapointID in (2033, 2035, 2043, 2037, 2031)
AND qb2.DatapointID=2003
GROUP BY qb1.CompanyName, qb1.AssetName, qb1.Year;
从您在评论中提到的错误:
错误“您尝试执行不包含指定表达式“CompanyName”作为聚合函数一部分的查询”
使用聚合函数要求您按 SELECT 列表中显示的列(聚合列除外)进行分组。
Edit:
我认为这就是您正在寻找的:
SELECT
qb1.CompanyName,
qb1.AssetName,
qb1.Year,
qb3.MPPOilRevised - TotalDataPointValue - TotalDataPointValueFactor
FROM
((
SELECT
qb1.CompanyName,
qb1.AssetName,
qb1.Year,
SUM(qb1.DatapointValue) 'TotalDataPointValue',
SUM(qb2.DatapointValue * 1000000) 'TotalDataPointValueFactor'
FROM
(PEBaseQuery AS qb1
INNER JOIN PEBaseQuery AS qb2
ON qb1.Year = qb2.Year AND qb1.AssetName = qb2.AssetName)
WHERE
qb1.DatapointID in (2033, 2035, 2043, 2037, 2031)
AND qb2.DatapointID = 2003
GROUP BY qb1.CompanyName, qb1.AssetName, qb1.Year
) qb1
INNER JOIN PE_MPPOilRevised AS qb3
ON qb1.Year = qb3.Year AND qb1.AssetName=qb3.AssetName)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)