No Current Record Error in MS Access 2010 while us

Question

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

The old subquery was like this:

(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.

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

Answer 1

Please use:

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

Answer 2

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 ...

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.

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)