Results 1 to 3 of 3

Thread: Nested SQL

  1. #1
    Join Date
    Dec 2002
    Posts
    97

    Question Unanswered: Nested SQL

    Okay,

    I've checked some of the threads here on nesting SQL statements and I think I'm still missing something.

    Could I borrow a few sets of eyes to look at this and see what I'm missing?

    Thanks.
    Chris


    ----------------TEXT FOLLOWS-------------------

    SELECT A.Soc, A.Fnd_ID, Sum(A.Tot_EPD) AS SumOfTot_EPD
    FROM tblJTEarnPd AS A INNER JOIN [SELECT Soc
    FROM tblJTGrad
    WHERE (((Mid([yr],5,2))='01') AND ((Left([yr],4))='2005') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='02') AND ((Left([yr],4))='2005') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='03') AND ((Left([yr],4))='2005') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='04') AND ((Left([yr],4))='2005') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='05') AND ((Left([yr],4))='2005') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='06') AND ((Left([yr],4))='2005') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='07') AND ((Left([yr],4))='2004') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='08') AND ((Left([yr],4))='2004') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='09') AND ((Left([yr],4))='2004') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='10') AND ((Left([yr],4))='2004') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='11') AND ((Left([yr],4))='2004') AND ((Left([Deg],1))='B'))
    OR (((Mid([yr],5,2))='12') AND ((Left([yr],4))='2004') AND ((Left([Deg],1))='B'))]. AS B ON (A.Soc = B.Soc)
    GROUP BY A.Soc, A.Fnd_ID
    HAVING (((Sum(A.Tot_EPD))>0));

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use paranthesis instead of square brackets.

    Also, you could use set logic here:
    Code:
    WHERE 
    (((Mid([yr],5,2))='01') AND ((Left([yr],4))='2005') AND ((Left([Deg],1))='B')) 
    
    OR 
    
    ( ((Left([yr],4))='2005') AND ((Left([Deg],1))='B')) 
       AND 
    ((Mid([yr],5,2) IN ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12')))
    Last edited by Teddy; 04-17-06 at 17:40.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2002
    Posts
    97

    Nope

    Nope...didn't work.

    The square bracket works if I take out the WHERE statements.

    From what I've figured out so far is that Access apparently doesn't like the LEFT, RIGHT, and MID statements in nested SQL.

    For instance, the following code works fine.

    SELECT A.Soc, A.Fnd_ID, Sum(A.Tot_EPD) AS SumOfTot_EPD
    FROM tblJTEarnPd AS A INNER JOIN [SELECT Soc, YR
    FROM tblJTGrad]. AS B ON (A.Soc = B.Soc)
    GROUP BY A.Soc, A.Fnd_ID
    HAVING (((Sum(A.Tot_EPD))>0));

    But what I posted earlier...not so much.

    -C
    Last edited by Mr_Yabbo; 04-17-06 at 17:33.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •