引用查询之间的字段值(Referencing field values between querie

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

Answer 1:

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

的错误“试图执行一个查询，不包括指定表达式‘公司名称’作为聚合函数”的一部分

聚合函数的使用要求您通过组出现在比聚合列以外的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)

Answer 2:

当您创建汇总查询，每个选定字段必须被聚合或分组。

尝试：

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

文章来源: Referencing field values between queries