Bdtjtk

I am facing problem in using nested queries in MS access. Those queries are working fine in sql developer.

Access SQL:-

select 

  B.prDate,

  B.Month, 

  (select C.Entity_Id,avg(C.prclose) 

   from **B** as C 

   where C.Month>=B.Month 

     and C.Month <= B.Month+3 

   group by C.Entity_Id)

from (

  select  A.Entity_Id, A.prDate, A.prclose , 

   (

    (SELECT max(ID) 

     FROM sheet1 where  Entity_Id=A.Entity_Id 

    ) - 

    (SELECT ID 

     FROM sheet1 where  ID=A.Id) +1

   ) as  Month  

  from sheet1 as A 

  order by Month, A.Entity_Id asc

) B 

Or

you can replace above alias with below query

select B.prDate, B.Month , ((select C.Entity_Id,avg(C.prclose) from 

(select  A.Entity_Id,A.prDate,A.prclose , ((SELECT max(ID) FROM sheet1 where  Entity_Id=A.Entity_Id ) -  (SELECT ID FROM sheet1 where  ID=A.Id ) +1) as  Month  from sheet1 as A order by Month, A.Entity_Id asc) B  as C where C.Month>=B.Month and  C.Month <= B.Month+3 group by C.Entity_Id  )) 



from 



(select  A.Entity_Id,A.prDate,A.prclose , ((SELECT max(ID) FROM sheet1 where  Entity_Id=A.Entity_Id ) -  (SELECT ID FROM sheet1 where  ID=A.Id ) +1) as  Month  from sheet1 as A order by Month, A.Entity_Id asc) B 

Other SQL:-

select B.prDate, B.Month , (select C.Entity_Id,avg(C.prclose) from B) as C where C.Month>=B.Month and  C.Month <= B.Month+3 group by C.Entity_Id   from  

(select  A.Entity_Id,A.prDate,A.prclose , ((SELECT max(ID) FROM sheet1 where  Entity_Id=A.Entity_Id ) -  (SELECT ID FROM sheet1 where  ID=A.Id ) +1) as  Month  from sheet1 as A order by Month, A.Entity_Id asc) B 

In SELECT clause subqueries, you cannot return more than one field as you attempt with aggregate query. To resolve, because you need to reuse B, consider saving this resultset in an intermediate, external query and reference it in a final query:

Intermediate Query (save as MS Access query object; last nested SELECT is redundant)

SELECT A.Entity_Id, A.prDate, A.prclose, 

       ((SELECT MAX(sub.ID) 

         FROM sheet1 sub

         WHERE sub.Entity_Id = A.Entity_Id) - sub.ID) + 1 AS [Month]

FROM sheet1 as A

Final Query

SELECT q.Entity_Id, q.prDate, q.[Month],

       (SELECT AVG(sub_q.prclose)

        FROM mySavedQuery AS sub_q

        WHERE sub_q.Entity_Id = q.Entity_Id

          AND sub_q.[Month] >= q.[Month] 

          AND sub_q.[Month] <= q.[Month] + 3 

       ) AS Avg_PrClose

FROM mySavedQuery AS q

ORDER BY q.[Month], q.Entity_Id

When the day comes when MS Access SQL supports CTE (or you upsize to an enterprise RDBMS), you can turn the saved query object into WITH clause for one statement:

WITH mySavedQuery AS

  (SELECT A.Entity_Id, A.prDate, A.prclose, 

          ((SELECT MAX(sub.ID) 

            FROM sheet1 sub

            WHERE sub.Entity_Id = A.Entity_Id) - sub.ID) + 1 AS [Month]

   FROM sheet1 as A)



SELECT q.Entity_Id, q.prDate, q.[Month],

       (SELECT AVG(sub_q.prclose)

        FROM mySavedQuery AS sub_q

        WHERE sub_q.Entity_Id = q.Entity_Id

          AND sub_q.[Month] >= q.[Month] 

          AND sub_q.[Month] <= q.[Month] + 3 

       ) AS Avg_PrClose

FROM mySavedQuery AS q

ORDER BY q.[Month], q.Entity_Id