By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

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

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

The old subquery was like this:
Expand|Select|Wrap|Line Numbers
  1. (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:
Expand|Select|Wrap|Line Numbers
  1. (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,
Nov 15 '11 #1
Share this Question
Share on Google+
5 Replies


patjones
Expert 100+
P: 931
Can you post the larger context in which you are using the sub-query? In other words, the containing query also. Thanks.
Nov 15 '11 #2

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 16 '11 #3

P: 5
Here is the whole code

Expand|Select|Wrap|Line Numbers
  1. SELECT People.ID, People.aacode, People.PERSNO, People.HRP, People.DVHsize, xMarSta.Marital, People.AgeCat,
  2. [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,
  3.  (SELECT Fam3.R01 FROM People AS Fam3 WHERE Fam3.aacode = People.aacode AND Fam3.PERSNO = 3) AS Rel3,
  4.  (SELECT Fam4.R01 FROM People AS Fam4 WHERE Fam4.aacode = People.aacode AND Fam4.PERSNO = 4) AS Rel4,
  5.  (SELECT Fam5.R01 FROM People AS Fam5 WHERE Fam5.aacode = People.aacode AND Fam5.PERSNO = 5) AS Rel5,
  6.  (SELECT Fam6.R01 FROM People AS Fam6 WHERE Fam6.aacode = People.aacode AND Fam6.PERSNO = 6) AS Rel6,
  7.  (SELECT Fam7.R01 FROM People AS Fam7 WHERE Fam7.aacode = People.aacode AND Fam7.PERSNO = 7) AS Rel7,
  8.  (SELECT Fam8.R01 FROM People AS Fam8 WHERE Fam8.aacode = People.aacode AND Fam8.PERSNO = 8) AS Rel8,
  9.  (SELECT Fam9.R01 FROM People AS Fam9 WHERE Fam9.aacode = People.aacode AND Fam9.PERSNO = 9) AS Rel9,
  10.  (SELECT Fam10.R01 FROM People AS Fam10 WHERE Fam10.aacode = People.aacode AND Fam10.PERSNO = 10) AS Rel10,
  11.  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,
  12.  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,
  13.  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,
  14.  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,
  15.  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,
  16.  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,
  17.  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,
  18.  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,
  19.  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,
  20.  (SELECT [People].[AgeCat] FROM [People] INNER JOIN [People] AS P2 ON ([People].[aacode] = [P2].[aacode]) WHERE [P2].[PERSNO] = 2) AS RAge2,
  21.  (Select FAge3.AgeCat FROM People AS FAge3 WHERE FAge3.aacode = People.aacode AND FAge3.PERSNO = 3) AS RAge3,
  22.  (Select FAge4.AgeCat FROM People AS FAge4 WHERE FAge4.aacode = People.aacode AND FAge4.PERSNO = 4) AS RAge4,
  23.  (Select FAge5.AgeCat FROM People AS FAge5 WHERE FAge5.aacode = People.aacode AND FAge5.PERSNO = 5) AS RAge5,
  24.  (Select FAge6.AgeCat FROM People AS FAge6 WHERE FAge6.aacode = People.aacode AND FAge6.PERSNO = 6) AS RAge6,
  25.  (Select FAge7.AgeCat FROM People AS FAge7 WHERE FAge7.aacode = People.aacode AND FAge7.PERSNO = 7) AS RAge7,
  26.  (Select FAge8.AgeCat FROM People AS FAge8 WHERE FAge8.aacode = People.aacode AND FAge8.PERSNO = 8) AS RAge8,
  27.  (Select FAge9.AgeCat FROM People AS FAge9 WHERE FAge9.aacode = People.aacode AND FAge9.PERSNO = 9) AS RAge9,
  28.  (Select FAge10.AgeCat FROM People AS FAge10 WHERE FAge10.aacode = People.aacode AND FAge10.PERSNO = 10) AS RAge10
  29. FROM xMarSta RIGHT JOIN People ON xMarSta.ID = People.xMarSta
  30. WHERE (((People.HRP)=[People.PERSNO]))
  31. ORDER BY People.aacode;
  32.  
Nov 18 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tP.ID
  2.      , tP.aacode
  3.      , tP.PERSNO
  4.      , tP.HRP
  5.      , tP.DVHsize
  6.      , xMarSta.Marital
  7.      , tP.AgeCat
  8.      , [Marital] & " (" & [AgeCat] & ")" & [RAL2] & [RAge2] & [RAL3] & [RAge3] & [RAL4] & [RAge4] &
  9.                    [RAL5] & [RAge5] & [RAL6] & [RAge6] & [RAL7] & [RAge7] & [RAL8] & [RAge8] AS HsTyp
  10.      , (SELECT Fam2.R01 FROM People AS Fam2 WHERE Fam2.aacode = tP.aacode AND Fam2.PERSNO = 2) AS Rel2
  11.      , (SELECT Fam3.R01 FROM People AS Fam3 WHERE Fam3.aacode = tP.aacode AND Fam3.PERSNO = 3) AS Rel3
  12.      , (SELECT Fam4.R01 FROM People AS Fam4 WHERE Fam4.aacode = tP.aacode AND Fam4.PERSNO = 4) AS Rel4
  13.      , (SELECT Fam5.R01 FROM People AS Fam5 WHERE Fam5.aacode = tP.aacode AND Fam5.PERSNO = 5) AS Rel5
  14.      , (SELECT Fam6.R01 FROM People AS Fam6 WHERE Fam6.aacode = tP.aacode AND Fam6.PERSNO = 6) AS Rel6
  15.      , (SELECT Fam7.R01 FROM People AS Fam7 WHERE Fam7.aacode = tP.aacode AND Fam7.PERSNO = 7) AS Rel7
  16.      , (SELECT Fam8.R01 FROM People AS Fam8 WHERE Fam8.aacode = tP.aacode AND Fam8.PERSNO = 8) AS Rel8
  17.      , (SELECT Fam9.R01 FROM People AS Fam9 WHERE Fam9.aacode = tP.aacode AND Fam9.PERSNO = 9) AS Rel9
  18.      , (SELECT Fam10.R01 FROM People AS Fam10 WHERE Fam10.aacode = tP.aacode AND Fam10.PERSNO = 10) AS Rel10
  19.      , Switch([Rel2] Is Null,Null,[Rel2]=-9,'DNA',[Rel2]=-8,'NoAns',[Rel2]=1,'Spouse'
  20.              ,[Rel2]=2,'Cohabitee',[Rel2]<7,'Child',[Rel2]<10,'Parent',[Rel2]<15,'Sibling'
  21.              ,[Rel2]=15,'Grandchild',[Rel2]=16,'Grandparent',[Rel2]=17,'OtherRelative'
  22.              ,[Rel2]=20,'CivilPartner',True,'Other') AS RAL2
  23.      , Switch([Rel3] Is Null,Null,[Rel3]=-9,'DNA',[Rel3]=-8,'NoAns',[Rel3]=1,'Spouse'
  24.              ,[Rel3]=2,'Cohabitee',[Rel3]<7,'Child',[Rel3]<10,'Parent',[Rel3]<15,'Sibling'
  25.              ,[Rel3]=15,'Grandchild',[Rel3]=16,'Grandparent',[Rel3]=17,'OtherRelative'
  26.              ,[Rel3]=20,'CivilPartner',True,'Other') AS RAL3
  27.      , Switch([Rel4] Is Null,Null,[Rel4]=-9,'DNA',[Rel4]=-8,'NoAns',[Rel4]=1,'Spouse'
  28.              ,[Rel4]=2,'Cohabitee',[Rel4]<7,'Child',[Rel4]<10,'Parent',[Rel4]<15,'Sibling'
  29.              ,[Rel4]=15,'Grandchild',[Rel4]=16,'Grandparent',[Rel4]=17,'OtherRelative'
  30.              ,[Rel4]=20,'CivilPartner',True,'Other') AS RAL4
  31.      , Switch([Rel5] Is Null,Null,[Rel5]=-9,'DNA',[Rel5]=-8,'NoAns',[Rel5]=1,'Spouse'
  32.              ,[Rel5]=2,'Cohabitee',[Rel5]<7,'Child',[Rel5]<10,'Parent',[Rel5]<15,'Sibling'
  33.              ,[Rel5]=15,'Grandchild',[Rel5]=16,'Grandparent',[Rel5]=17,'OtherRelative'
  34.              ,[Rel5]=20,'CivilPartner',True,'Other') AS RAL5
  35.      , Switch([Rel6] Is Null,Null,[Rel6]=-9,'DNA',[Rel6]=-8,'NoAns',[Rel6]=1,'Spouse'
  36.              ,[Rel6]=2,'Cohabitee',[Rel6]<7,'Child',[Rel6]<10,'Parent',[Rel6]<15,'Sibling'
  37.              ,[Rel6]=15,'Grandchild',[Rel6]=16,'Grandparent',[Rel6]=17,'OtherRelative'
  38.              ,[Rel6]=20,'CivilPartner',True,'Other') AS RAL6
  39.      , Switch([Rel7] Is Null,Null,[Rel7]=-9,'DNA',[Rel7]=-8,'NoAns',[Rel7]=1,'Spouse'
  40.              ,[Rel7]=2,'Cohabitee',[Rel7]<7,'Child',[Rel7]<10,'Parent',[Rel7]<15,'Sibling'
  41.              ,[Rel7]=15,'Grandchild',[Rel7]=16,'Grandparent',[Rel7]=17,'OtherRelative'
  42.              ,[Rel7]=20,'CivilPartner',True,'Other') AS RAL7
  43.      , Switch([Rel8] Is Null,Null,[Rel8]=-9,'DNA',[Rel8]=-8,'NoAns',[Rel8]=1,'Spouse'
  44.              ,[Rel8]=2,'Cohabitee',[Rel8]<7,'Child',[Rel8]<10,'Parent',[Rel8]<15,'Sibling'
  45.              ,[Rel8]=15,'Grandchild',[Rel8]=16,'Grandparent',[Rel8]=17,'OtherRelative'
  46.              ,[Rel8]=20,'CivilPartner',True,'Other') AS RAL8
  47.      , Switch([Rel9] Is Null,Null,[Rel9]=-9,'DNA',[Rel9]=-8,'NoAns',[Rel9]=1,'Spouse'
  48.              ,[Rel9]=2,'Cohabitee',[Rel9]<7,'Child',[Rel9]<10,'Parent',[Rel9]<15,'Sibling'
  49.              ,[Rel9]=15,'Grandchild',[Rel9]=16,'Grandparent',[Rel9]=17,'OtherRelative'
  50.              ,[Rel9]=20,'CivilPartner',True,'Other') AS RAL9
  51.      , Switch([Rel10] Is Null,Null,[Rel10]=-9,'DNA',[Rel10]=-8,'NoAns',[Rel10]=1,'Spouse'
  52.              ,[Rel10]=2,'Cohabitee',[Rel10]<7,'Child',[Rel10]<10,'Parent',[Rel10]<15,'Sibling'
  53.              ,[Rel10]=15,'Grandchild',[Rel10]=16,'Grandparent',[Rel10]=17,'OtherRelative'
  54.              ,[Rel10]=20,'CivilPartner',True,'Other') AS RAL10
  55.      , (SELECT [People].[AgeCat] FROM [People] INNER JOIN [People] AS P2 ON ([People].[aacode] = [P2].[aacode]) WHERE [P2].[PERSNO] = 2) AS RAge2
  56.      , (SELECT FAge3.AgeCat FROM People AS FAge3 WHERE FAge3.aacode = tP.aacode AND FAge3.PERSNO = 3) AS RAge3
  57.      , (SELECT FAge4.AgeCat FROM People AS FAge4 WHERE FAge4.aacode = tP.aacode AND FAge4.PERSNO = 4) AS RAge4
  58.      , (SELECT FAge5.AgeCat FROM People AS FAge5 WHERE FAge5.aacode = tP.aacode AND FAge5.PERSNO = 5) AS RAge5
  59.      , (SELECT FAge6.AgeCat FROM People AS FAge6 WHERE FAge6.aacode = tP.aacode AND FAge6.PERSNO = 6) AS RAge6
  60.      , (SELECT FAge7.AgeCat FROM People AS FAge7 WHERE FAge7.aacode = tP.aacode AND FAge7.PERSNO = 7) AS RAge7
  61.      , (SELECT FAge8.AgeCat FROM People AS FAge8 WHERE FAge8.aacode = tP.aacode AND FAge8.PERSNO = 8) AS RAge8
  62.      , (SELECT FAge9.AgeCat FROM People AS FAge9 WHERE FAge9.aacode = tP.aacode AND FAge9.PERSNO = 9) AS RAge9
  63.      , (SELECT FAge10.AgeCat FROM People AS FAge10 WHERE FAge10.aacode = tP.aacode AND FAge10.PERSNO = 10) AS RAge10
  64.  
  65. FROM     xMarSta
  66.          RIGHT JOIN 
  67.          People AS tP
  68.   ON     xMarSta.ID = tP.xMarSta
  69.  
  70. WHERE    (tP.HRP = tP.PERSNO)
  71.  
  72. 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.
Nov 18 '11 #5

patjones
Expert 100+
P: 931
This SQL seems to reflect a problem with the underlying table structure. It looks like it needs to be normalized.
Nov 21 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.