No Current Record Error in Access 2010 while using a self join

[falcs]

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] AND [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,

[patjones]

Can you post the larger context in which you are using the sub-query? In other words, the containing query also. Thanks.

[NeoPa]

If the outer SQL uses [People] too (which I expect it does) then the outer SQL also needs an ALIAS (FROM [People] AS [SomeAlias]) and you refer to the fields from the different versions of the same table via both of their ALIASes.

PS. As Pat implies it would have made a lot more sense to include that information in the question. It's hard to imagine anyone could fail to realise that information is necessary in the circumstances.

[falcs]

Here is the whole code

SELECT People.ID, People.aacode, People.PERSNO, People.HRP, People.DVHsize, xMarSta.Marital, People.AgeCat,
[Marital] & " (" & [AgeCat] & ")" & [RAL2] & [RAge2] & [RAL3] & [RAge3] & [RAL4] & [RAge4] & [RAL5] & [RAge5] & [RAL6] & [RAge6] & [RAL7] & [RAge7] & [RAL8] & [RAge8] AS HsTyp, (SELECT Fam2.R01 FROM People AS Fam2 WHERE Fam2.aacode = People.aacode AND Fam2.PERSNO = 2) AS Rel2,
 (SELECT Fam3.R01 FROM People AS Fam3 WHERE Fam3.aacode = People.aacode AND Fam3.PERSNO = 3) AS Rel3,
 (SELECT Fam4.R01 FROM People AS Fam4 WHERE Fam4.aacode = People.aacode AND Fam4.PERSNO = 4) AS Rel4,
 (SELECT Fam5.R01 FROM People AS Fam5 WHERE Fam5.aacode = People.aacode AND Fam5.PERSNO = 5) AS Rel5,
 (SELECT Fam6.R01 FROM People AS Fam6 WHERE Fam6.aacode = People.aacode AND Fam6.PERSNO = 6) AS Rel6,
 (SELECT Fam7.R01 FROM People AS Fam7 WHERE Fam7.aacode = People.aacode AND Fam7.PERSNO = 7) AS Rel7,
 (SELECT Fam8.R01 FROM People AS Fam8 WHERE Fam8.aacode = People.aacode AND Fam8.PERSNO = 8) AS Rel8,
 (SELECT Fam9.R01 FROM People AS Fam9 WHERE Fam9.aacode = People.aacode AND Fam9.PERSNO = 9) AS Rel9,
 (SELECT Fam10.R01 FROM People AS Fam10 WHERE Fam10.aacode = People.aacode AND Fam10.PERSNO = 10) AS Rel10,
 Switch([Rel2] Is Null,Null,[Rel2]=-9,'DNA',[Rel2]=-8,'NoAns',[Rel2]=1,'Spouse',[Rel2]=2,'Cohabitee',[Rel2]<7,'Child',[Rel2]<10,'Parent',[Rel2]<15,'Sibling',[Rel2]=15,'Grandchild',[Rel2]=16,'Grandparent',[Rel2]=17,'OtherRelative',[Rel2]=20,'CivilPartner',True,'Other') AS RAL2,
 Switch([Rel3] Is Null,Null,[Rel3]=-9,'DNA',[Rel3]=-8,'NoAns',[Rel3]=1,'Spouse',[Rel3]=2,'Cohabitee',[Rel3]<7,'Child',[Rel3]<10,'Parent',[Rel3]<15,'Sibling',[Rel3]=15,'Grandchild',[Rel3]=16,'Grandparent',[Rel3]=17,'OtherRelative',[Rel3]=20,'CivilPartner',True,'Other') AS RAL3,
 Switch([Rel4] Is Null,Null,[Rel4]=-9,'DNA',[Rel4]=-8,'NoAns',[Rel4]=1,'Spouse',[Rel4]=2,'Cohabitee',[Rel4]<7,'Child',[Rel4]<10,'Parent',[Rel4]<15,'Sibling',[Rel4]=15,'Grandchild',[Rel4]=16,'Grandparent',[Rel4]=17,'OtherRelative',[Rel4]=20,'CivilPartner',True,'Other') AS RAL4,
 Switch([Rel5] Is Null,Null,[Rel5]=-9,'DNA',[Rel5]=-8,'NoAns',[Rel5]=1,'Spouse',[Rel5]=2,'Cohabitee',[Rel5]<7,'Child',[Rel5]<10,'Parent',[Rel5]<15,'Sibling',[Rel5]=15,'Grandchild',[Rel5]=16,'Grandparent',[Rel5]=17,'OtherRelative',[Rel5]=20,'CivilPartner',True,'Other') AS RAL5,
 Switch([Rel6] Is Null,Null,[Rel6]=-9,'DNA',[Rel6]=-8,'NoAns',[Rel6]=1,'Spouse',[Rel6]=2,'Cohabitee',[Rel6]<7,'Child',[Rel6]<10,'Parent',[Rel6]<15,'Sibling',[Rel6]=15,'Grandchild',[Rel6]=16,'Grandparent',[Rel6]=17,'OtherRelative',[Rel6]=20,'CivilPartner',True,'Other') AS RAL6,
 Switch([Rel7] Is Null,Null,[Rel7]=-9,'DNA',[Rel7]=-8,'NoAns',[Rel7]=1,'Spouse',[Rel7]=2,'Cohabitee',[Rel7]<7,'Child',[Rel7]<10,'Parent',[Rel7]<15,'Sibling',[Rel7]=15,'Grandchild',[Rel7]=16,'Grandparent',[Rel7]=17,'OtherRelative',[Rel7]=20,'CivilPartner',True,'Other') AS RAL7,
 Switch([Rel8] Is Null,Null,[Rel8]=-9,'DNA',[Rel8]=-8,'NoAns',[Rel8]=1,'Spouse',[Rel8]=2,'Cohabitee',[Rel8]<7,'Child',[Rel8]<10,'Parent',[Rel8]<15,'Sibling',[Rel8]=15,'Grandchild',[Rel8]=16,'Grandparent',[Rel8]=17,'OtherRelative',[Rel8]=20,'CivilPartner',True,'Other') AS RAL8,
 Switch([Rel9] Is Null,Null,[Rel9]=-9,'DNA',[Rel9]=-8,'NoAns',[Rel9]=1,'Spouse',[Rel9]=2,'Cohabitee',[Rel9]<7,'Child',[Rel9]<10,'Parent',[Rel9]<15,'Sibling',[Rel9]=15,'Grandchild',[Rel9]=16,'Grandparent',[Rel9]=17,'OtherRelative',[Rel9]=20,'CivilPartner',True,'Other') AS RAL9,
 Switch([Rel10] Is Null,Null,[Rel10]=-9,'DNA',[Rel10]=-8,'NoAns',[Rel10]=1,'Spouse',[Rel10]=2,'Cohabitee',[Rel10]<7,'Child',[Rel10]<10,'Parent',[Rel10]<15,'Sibling',[Rel10]=15,'Grandchild',[Rel10]=16,'Grandparent',[Rel10]=17,'OtherRelative',[Rel10]=20,'CivilPartner',True,'Other') AS RAL10,
 (SELECT [People].[AgeCat] FROM [People] INNER JOIN [People] AS P2 ON ([People].[aacode] = [P2].[aacode]) WHERE [P2].[PERSNO] = 2) AS RAge2,
 (Select FAge3.AgeCat FROM People AS FAge3 WHERE FAge3.aacode = People.aacode AND FAge3.PERSNO = 3) AS RAge3,
 (Select FAge4.AgeCat FROM People AS FAge4 WHERE FAge4.aacode = People.aacode AND FAge4.PERSNO = 4) AS RAge4,
 (Select FAge5.AgeCat FROM People AS FAge5 WHERE FAge5.aacode = People.aacode AND FAge5.PERSNO = 5) AS RAge5,
 (Select FAge6.AgeCat FROM People AS FAge6 WHERE FAge6.aacode = People.aacode AND FAge6.PERSNO = 6) AS RAge6,
 (Select FAge7.AgeCat FROM People AS FAge7 WHERE FAge7.aacode = People.aacode AND FAge7.PERSNO = 7) AS RAge7,
 (Select FAge8.AgeCat FROM People AS FAge8 WHERE FAge8.aacode = People.aacode AND FAge8.PERSNO = 8) AS RAge8,
 (Select FAge9.AgeCat FROM People AS FAge9 WHERE FAge9.aacode = People.aacode AND FAge9.PERSNO = 9) AS RAge9,
 (Select FAge10.AgeCat FROM People AS FAge10 WHERE FAge10.aacode = People.aacode AND FAge10.PERSNO = 10) AS RAge10
FROM xMarSta RIGHT JOIN People ON xMarSta.ID = People.xMarSta
WHERE (((People.HRP)=[People.PERSNO]))
ORDER BY People.aacode;

[NeoPa]

You probably know already, but this is, frankly, extraordinarily clumsy SQL code.

I've changed the layout so it can be understood more easily, and illustrated the change required to refer to the various different versions of the [People] table. I've also fixed a few buggy references in there somewhere, but that does not make this good SQL.

SELECT tP.ID
     , tP.aacode
     , tP.PERSNO
     , tP.HRP
     , tP.DVHsize
     , xMarSta.Marital
     , tP.AgeCat
     , [Marital] & " (" & [AgeCat] & ")" & [RAL2] & [RAge2] & [RAL3] & [RAge3] & [RAL4] & [RAge4] &
                   [RAL5] & [RAge5] & [RAL6] & [RAge6] & [RAL7] & [RAge7] & [RAL8] & [RAge8] AS HsTyp
     , (SELECT Fam2.R01 FROM People AS Fam2 WHERE Fam2.aacode = tP.aacode AND Fam2.PERSNO = 2) AS Rel2
     , (SELECT Fam3.R01 FROM People AS Fam3 WHERE Fam3.aacode = tP.aacode AND Fam3.PERSNO = 3) AS Rel3
     , (SELECT Fam4.R01 FROM People AS Fam4 WHERE Fam4.aacode = tP.aacode AND Fam4.PERSNO = 4) AS Rel4
     , (SELECT Fam5.R01 FROM People AS Fam5 WHERE Fam5.aacode = tP.aacode AND Fam5.PERSNO = 5) AS Rel5
     , (SELECT Fam6.R01 FROM People AS Fam6 WHERE Fam6.aacode = tP.aacode AND Fam6.PERSNO = 6) AS Rel6
     , (SELECT Fam7.R01 FROM People AS Fam7 WHERE Fam7.aacode = tP.aacode AND Fam7.PERSNO = 7) AS Rel7
     , (SELECT Fam8.R01 FROM People AS Fam8 WHERE Fam8.aacode = tP.aacode AND Fam8.PERSNO = 8) AS Rel8
     , (SELECT Fam9.R01 FROM People AS Fam9 WHERE Fam9.aacode = tP.aacode AND Fam9.PERSNO = 9) AS Rel9
     , (SELECT Fam10.R01 FROM People AS Fam10 WHERE Fam10.aacode = tP.aacode AND Fam10.PERSNO = 10) AS Rel10
     , Switch([Rel2] Is Null,Null,[Rel2]=-9,'DNA',[Rel2]=-8,'NoAns',[Rel2]=1,'Spouse'
             ,[Rel2]=2,'Cohabitee',[Rel2]<7,'Child',[Rel2]<10,'Parent',[Rel2]<15,'Sibling'
             ,[Rel2]=15,'Grandchild',[Rel2]=16,'Grandparent',[Rel2]=17,'OtherRelative'
             ,[Rel2]=20,'CivilPartner',True,'Other') AS RAL2
     , Switch([Rel3] Is Null,Null,[Rel3]=-9,'DNA',[Rel3]=-8,'NoAns',[Rel3]=1,'Spouse'
             ,[Rel3]=2,'Cohabitee',[Rel3]<7,'Child',[Rel3]<10,'Parent',[Rel3]<15,'Sibling'
             ,[Rel3]=15,'Grandchild',[Rel3]=16,'Grandparent',[Rel3]=17,'OtherRelative'
             ,[Rel3]=20,'CivilPartner',True,'Other') AS RAL3
     , Switch([Rel4] Is Null,Null,[Rel4]=-9,'DNA',[Rel4]=-8,'NoAns',[Rel4]=1,'Spouse'
             ,[Rel4]=2,'Cohabitee',[Rel4]<7,'Child',[Rel4]<10,'Parent',[Rel4]<15,'Sibling'
             ,[Rel4]=15,'Grandchild',[Rel4]=16,'Grandparent',[Rel4]=17,'OtherRelative'
             ,[Rel4]=20,'CivilPartner',True,'Other') AS RAL4
     , Switch([Rel5] Is Null,Null,[Rel5]=-9,'DNA',[Rel5]=-8,'NoAns',[Rel5]=1,'Spouse'
             ,[Rel5]=2,'Cohabitee',[Rel5]<7,'Child',[Rel5]<10,'Parent',[Rel5]<15,'Sibling'
             ,[Rel5]=15,'Grandchild',[Rel5]=16,'Grandparent',[Rel5]=17,'OtherRelative'
             ,[Rel5]=20,'CivilPartner',True,'Other') AS RAL5
     , Switch([Rel6] Is Null,Null,[Rel6]=-9,'DNA',[Rel6]=-8,'NoAns',[Rel6]=1,'Spouse'
             ,[Rel6]=2,'Cohabitee',[Rel6]<7,'Child',[Rel6]<10,'Parent',[Rel6]<15,'Sibling'
             ,[Rel6]=15,'Grandchild',[Rel6]=16,'Grandparent',[Rel6]=17,'OtherRelative'
             ,[Rel6]=20,'CivilPartner',True,'Other') AS RAL6
     , Switch([Rel7] Is Null,Null,[Rel7]=-9,'DNA',[Rel7]=-8,'NoAns',[Rel7]=1,'Spouse'
             ,[Rel7]=2,'Cohabitee',[Rel7]<7,'Child',[Rel7]<10,'Parent',[Rel7]<15,'Sibling'
             ,[Rel7]=15,'Grandchild',[Rel7]=16,'Grandparent',[Rel7]=17,'OtherRelative'
             ,[Rel7]=20,'CivilPartner',True,'Other') AS RAL7
     , Switch([Rel8] Is Null,Null,[Rel8]=-9,'DNA',[Rel8]=-8,'NoAns',[Rel8]=1,'Spouse'
             ,[Rel8]=2,'Cohabitee',[Rel8]<7,'Child',[Rel8]<10,'Parent',[Rel8]<15,'Sibling'
             ,[Rel8]=15,'Grandchild',[Rel8]=16,'Grandparent',[Rel8]=17,'OtherRelative'
             ,[Rel8]=20,'CivilPartner',True,'Other') AS RAL8
     , Switch([Rel9] Is Null,Null,[Rel9]=-9,'DNA',[Rel9]=-8,'NoAns',[Rel9]=1,'Spouse'
             ,[Rel9]=2,'Cohabitee',[Rel9]<7,'Child',[Rel9]<10,'Parent',[Rel9]<15,'Sibling'
             ,[Rel9]=15,'Grandchild',[Rel9]=16,'Grandparent',[Rel9]=17,'OtherRelative'
             ,[Rel9]=20,'CivilPartner',True,'Other') AS RAL9
     , Switch([Rel10] Is Null,Null,[Rel10]=-9,'DNA',[Rel10]=-8,'NoAns',[Rel10]=1,'Spouse'
             ,[Rel10]=2,'Cohabitee',[Rel10]<7,'Child',[Rel10]<10,'Parent',[Rel10]<15,'Sibling'
             ,[Rel10]=15,'Grandchild',[Rel10]=16,'Grandparent',[Rel10]=17,'OtherRelative'
             ,[Rel10]=20,'CivilPartner',True,'Other') AS RAL10
     , (SELECT [People].[AgeCat] FROM [People] INNER JOIN [People] AS P2 ON ([People].[aacode] = [P2].[aacode]) WHERE [P2].[PERSNO] = 2) AS RAge2
     , (SELECT FAge3.AgeCat FROM People AS FAge3 WHERE FAge3.aacode = tP.aacode AND FAge3.PERSNO = 3) AS RAge3
     , (SELECT FAge4.AgeCat FROM People AS FAge4 WHERE FAge4.aacode = tP.aacode AND FAge4.PERSNO = 4) AS RAge4
     , (SELECT FAge5.AgeCat FROM People AS FAge5 WHERE FAge5.aacode = tP.aacode AND FAge5.PERSNO = 5) AS RAge5
     , (SELECT FAge6.AgeCat FROM People AS FAge6 WHERE FAge6.aacode = tP.aacode AND FAge6.PERSNO = 6) AS RAge6
     , (SELECT FAge7.AgeCat FROM People AS FAge7 WHERE FAge7.aacode = tP.aacode AND FAge7.PERSNO = 7) AS RAge7
     , (SELECT FAge8.AgeCat FROM People AS FAge8 WHERE FAge8.aacode = tP.aacode AND FAge8.PERSNO = 8) AS RAge8
     , (SELECT FAge9.AgeCat FROM People AS FAge9 WHERE FAge9.aacode = tP.aacode AND FAge9.PERSNO = 9) AS RAge9
     , (SELECT FAge10.AgeCat FROM People AS FAge10 WHERE FAge10.aacode = tP.aacode AND FAge10.PERSNO = 10) AS RAge10

FROM     xMarSta
         RIGHT JOIN 
         People AS tP
  ON     xMarSta.ID = tP.xMarSta

WHERE    (tP.HRP = tP.PERSNO)

ORDER BY tP.aacode

PS. I don't say all this about your SQL just to whinge. I say it because you should probably be looking for an alternative way of handling your [Rel?], [Ral?] and [RAge?] fields.

[patjones]

This SQL seems to reflect a problem with the underlying table structure. It looks like it needs to be normalized.