我试图将一些计算出来的Excel到我的Access数据库，但是当我在上面5个输入值，用我面对“查询太复杂”的错误。 我应该将查询起来还是有解决工作中更有效的方式？ 任何帮助，将不胜感激！ 下面是代码：
SELECT qb1.CompanyName, qb1.Assetname, qb1.Year,
((qb1.DatapointValue*1000000)+qb2.DatapointValue+
qb3.DatapointValue+qb4.DatapointValue+qb5.DatapointValue+
qb6.DatapointValue) AS MPPOilRevised
FROM ((((((PEBaseQuery AS qb1
INNER JOIN PEBaseQuery AS qb2 ON qb1.AssetName=qb2.AssetName)
INNER JOIN PEBaseQuery AS qb3 ON qb1.AssetName=qb3.AssetName)
INNER JOIN PEBaseQuery AS qb4 ON qb1.AssetName=qb4.AssetName)
INNER JOIN PEBaseQuery AS qb5 ON qb1.AssetName=qb5.AssetName)
INNER JOIN PEBaseQuery AS qb6 ON qb1.AssetName=qb6.AssetName))
WHERE qb1.DatapointID=2003 And qb2.DatapointID=2032
And qb3.DatapointID=2034 And qb4.DatapointID=2042
And qb5.DatapointID=2036 And qb6.DatapointID=2030;
CompanyName, Year, AssetName, DatapointID, DatapointName, DatapointValue
CompanyA, 2011, CAAsset1, 2005, OIL, 170883.740972926
CompanyA, 2011, CAAsset1, 2032, Wellwork, 615913
CompanyA, 2011, CAAsset1, 2034, Annual shutdown, 0
CompanyA, 2011, CAAsset1, 2042, Export, 0
CompanyA, 2011, CAAsset1, 2036, Plant, 958387
CompanyA, 2011, CAAsset1, 2030, Reservoir, 2394231
它看起来像你需要的不是这种复杂的聚合查询。 例如
select companyName, assetName, year,
Sum(DatapointValue) as MPPOilRevised
from PEBaseQuery
where DatapointID in (2032, 2034, 2042, 2036)
group by companyName, assetName, year
唯一的问题是乘以1000000第一个数据点。 你可以尝试IIF为：
select companyName, assetName, year,
Sum(IIf(DatapointID=2003,DatapointValue*1000000,DatapointValue)) as MPPOilRevised
from PEBaseQuery
where DatapointID in (2032, 2034, 2042, 2036)
group by companyName, assetName, year
另外，请尝试这种“疯狂”的查询，与这个特殊的DatapointID子查询，而不IIF ：
select companyName, assetName, year, SUM(DatapointValue)
+ (select SUM(DatapointValue * 1000000) from PEBaseQuery q2
where q2.companyName = q1.companyName
and q2.assetName= q1.assetName
and q2.year= q1.year
and q2.DatapointID = 2003
group by companyName, assetName, year)
from PEBaseQuery q1
where DatapointID in (2032, 2034, 2042, 2036)
group by companyName, assetName, year
更新“生产最大潜力”。 请尝试以下操作：
select b.companyName, b.assetName, IIf(b.calculationResult > mp.calculationResult,b.calculationResult,mp.calculationResult) as MPPOilRevised
from
(select companyName, assetName, year, Sum(IIf(DatapointID=2003,DatapointValue*1000000,DatapointValue)) as calculationResult
from PEBaseQuery
where DatapointID in (2032, 2034, 2042, 2036)
group by companyName, assetName, year) b --Base
left join
(select companyName, assetName, year,
Sum(DatapointValue) as calculationResult
from PEBaseQuery
where DatapointID = 2218
group by companyName, assetName, year) mp -- Max Potential
on b.companyName= mp.companyName
and b.assetName = mp.assetName
and b.year = mp.year
用。减去逻辑计算的一个例子。 更新了最后的疯狂SQL。 另请注意，我将与应用逻辑或这样那样的事情存储手续：
select b.companyName, b.assetName, IIf(b.calculationResult > mp.calculationResult,b.calculationResult,mp.calculationResult) as MPPOilRevised
from
(select companyName, assetName, year, SUM(DatapointValue)
+ (select SUM(DatapointValue * 1000000) from PEBaseQuery q2
where q2.companyName = q1.companyName
and q2.assetName= q1.assetName
and q2.year= q1.year
and q2.DatapointID = 2003
group by companyName, assetName, year)
- (select SUM(DatapointValue) from PEBaseQuery q2
where q2.companyName = q1.companyName
and q2.assetName= q1.assetName
and q2.year= q1.year
and q2.DatapointID = 2029
group by companyName, assetName, year)
from PEBaseQuery q1
where DatapointID in (2032, 2034, 2042, 2036)
group by companyName, assetName, year) b --Base
left join
(select companyName, assetName, year,
Sum(DatapointValue) as calculationResult
from PEBaseQuery
where DatapointID = 2218
group by companyName, assetName, year) mp -- Max Potential
on b.companyName= mp.companyName
and b.assetName = mp.assetName
and b.year = mp.year
