在查询之间引用字段值

小编典典

在查询之间引用字段值

sql

我试图通过使用查询在Access中创建一个计算。目前，一个查询计算出“
MPP油”的值（最大生产潜力），而另一个查询需要使用该值来计算“未分配的损失”。这些计算使用来自基本查询“
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;

阅读 243

2021-04-14

共1个答案

小编典典

从您在评论中提到的错误：

错误“您试图执行不包含指定表达式“ CompanyName”作为聚合函数一部分的查询”

使用聚合函数要求您按SELECT列表中除聚合列以外的列进行分组。

编辑：

我认为这是您要寻找的：

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)

2021-04-14