在查询之间引用字段值

本文介绍了在查询之间引用字段值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试通过使用查询在Access中创建一个计算.目前，一个查询计算出"MPP油"的值(最大生产潜力)，而另一个查询需要使用该值来计算未分配的损失".这些计算使用来自基本查询"PEBaseQuery"的公司/资产/年份数据.其他输入值(用于计算未分配的损失)使用ID进行引用...虽然我的代码似乎有误，请帮忙！

Im trying to create a calculation in Access through the use of queries. At the moment one query calculates the value of 'MPP Oil' (max production potential) and another query needs to use this value to calculate 'Unallocated losses'. These calculations use company/asset/year data from a base query 'PEBaseQuery'. Other input values to calculate Unallocated losses are referenced using IDs... There seems to be something off with my code though, please help!

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;

推荐答案

从您在注释中提到的错误开始:

From the error you mentioned in the comments:

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

the error 'You tried to execute a query that does not include the specified expression 'CompanyName' as part of an aggregate function'

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

Use of aggregate functions require you to group by the columns that appear in the SELECT list other than the aggregated columns.

我认为这就是您要寻找的东西

I think this is what you are looking for:

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)