Converting MSSQL CTE query to access?

[mnty89]

I had this query formulated that was based off of a query through MSSQL and has CTE expressions which are not compatible with access I guess? I am a noob with Access and SQL. Is there a way to recreate this query but with compatibility with Access?


WITH CTE as
( SELECT SHIPMENT_XID,Original Weight,
SHIPMENT_XID as Grp,
0 as parent,
Original Weight as CSum,
1 as cnt,
CAST(SHIPMENT_XID as Varchar(MAX)) as path
from T where Original Weight<=45500
UNION all
SELECT t.SHIPMENT_XID,t.Original Weight,
CTE.Grp as Grp,
CTE.SHIPMENT_XID as parent,
T.Original Weight+CTE.CSum as CSum,
CTE.cnt+1 as cnt,
CTE.path+','+CAST(t.SHIPMENT_XID as Varchar(MAX)) as path
from T
JOIN CTE on T.Original Weight+CTE.CSum<=150
and CTE.SHIPMENT_XID<T.SHIPMENT_XID
),
BACK_CTE as
(select CTE.SHIPMENT_XID,CTE.Original Weight,CTE.grp,
CTE.path ,CTE.cnt as cnt,
CTE.parent,CSum
from CTE where CTE.CSum=150
union all
select CTE.SHIPMENT_XID,CTE.Original Weight,CTE.grp,
BACK_CTE.path,BACK_CTE.cnt,
CTE.parent,CTE.CSum
from CTE
JOIN BACK_CTE on CTE.SHIPMENT_XID=BACK_CTE.parent
and CTE.Grp=BACK_CTE.Grp
and BACK_CTE.CSum-BACK_CTE.Original Weight=CTE.CSum
)
select SHIPMENT_XID,Original Weight,path, cnt as ItemsCount from BACK_CTE order by cnt,path,SHIPMENT_XID

 

[xenou]

It's a slightly more advanced query than usual since it uses a recursive CTE strategy. In general, you'll have to break this down into separate queries that you can have calling each other. Not sure why you'd go from SQL Server to Access here.