使用自连接时，MS Access 2010中没有当前记录错误 [英] No Current Record Error in MS Access 2010 while using a self join

问题描述

我正在用自联接替换子查询，以提高查询性能.

I'm replacing a subquery with an self join to improve performance of my query.

旧的子查询是这样的:

(SELECT fage2.agecat 
 FROM   people AS fage2 
 WHERE  fage2.aacode = people.aacode 
       AND fage2.persno = 2) AS RAge2,

新的自我加入是这样的:

The new self join is like this:

(SELECT [People].[AgeCat] 
FROM   [People] 
       INNER JOIN [People] AS p2 
         ON [People].[aacode] = [P2].[aacode] 
WHERE  [P2].[PERSNO] = 2 ) AS RAge2,

但返回无当前记录"错误消息.

but returns a No Current Record error message.

目标是找到具有相同aacode但PERSNO编号为2的记录，并在名为RAge2的列中返回该记录的AgeCat，

The goal is to find the record that has the same aacode but has the PERSNO number of 2 and return the AgeCat for that record in a column called RAge2,

这只是较大查询中的一部分，完整查询时会完整解释

This is only part of a larger query which is explained in full Convert a SQL subquery into a join when looking at another record in the same table Access 2010

推荐答案

Huum，您要优化的查询看起来像是一个较大查询的一部分，对于发布整个查询的问题非常重要，因此有助于理解您的问题...

Huum, looks like this query that you want to optimize is part of a bigger query, and would be important to the question that you post the entire query so it would help on understanding your problem ...

此外，从我所看到的，您将显示具有相同AACode的两行的RAge2，不仅是您对目标说的具有Persno = 2的行.粘贴整个查询也将有助于理解这一点.

Also, from what I can see you would be showing the RAge2 for both rows with same AACode not only to the one that has Persno = 2 as you said on the goal. Pasting your entire query would help to understand that also.

我试图理解您的查询，所以为您的原始查询创建了一个 fake 查询:

I was trying to understand your query, so I created a fake query for your original one:

SELECT
(SELECT FAge2.AgeCat
 FROM People AS FAge2
 WHERE FAge2.aacode = People.aacode 
   AND FAge2.PERSNO = 2) AS RAge2,
People.PersonId
FROM People

要获得相同的结果，您将需要左联接而不是内部联接，因为带有子查询的查询不会从外部表中排除结果，因此，作为结果的联接查询，您将需要进行以下操作:

To get the same results you would need a Left Join and not a Inner Join as a query with a subquery wouldn't exclude results from the outer table, so you would have something like this as the resulting Join query:

SELECT 
 FAge2.AgeCat as RAge2,
 People.PersonID,
FROM People
Left JOIN People AS FAge2 ON (FAge2.aacode = People.aacode AND FAge2.PERSNO = 2)

这篇关于使用自连接时，MS Access 2010中没有当前记录错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！