Recursive SQL Query in MS Access

[skumar]

Hi Team,

I'm new to MS Access. Please help me with the below requirement.

I have two tables called tblParent and tblChild and I need to write a recursive query to get the final output as in below table 'Final Result'.

I've written T-SQL recursive query using CTE but it is not working in Access because it does not support CTE. So please help me how to write recursive query to achieve the below output.




Join: tblParent P JOIN tbl Child C ON C.PID = P.P_Code (recursive)

Below is my T-SQL Query:
;WITH CTE1 AS


(
SELECT P.[KeyID], 0 AS ChildKeyID, 1 AS Lvl, P.P_ID, P.R_Code, P.R_Quantity
FROM Parent P


UNION ALL


SELECT T.[KeyID], C.[KeyID], T.Lvl + 1, T.P_ID, C.R_Code, C.R_Quantity
FROM Child C
JOIN CTE1 T ON T.R_Code = C.P_ID
)
,
CTE2 AS
(
SELECT [KeyID], COUNT(*) AS CNT FROM CTE1
GROUP BY [KeyID]
)
SELECT T.P_ID, T.R_Code, T.R_Quantity
FROM CTE1 T
LEFT JOIN CTE2 C ON C.[KeyID] = T.[KeyID]
WHERE (ChildKeyID = 0 AND Lvl = 1 AND CNT = 1) OR (ChildKeyID <> 0 AND Lvl > 1)
ORDER BY T.[KeyID]
OPTION (MAXRECURSION 0)

[aytee111]

I was hoping someone who knows T-SQL would jump in and answer you. Access does not have recursive queries. First, you need to join the two tables on exact match fields and from your sample above there isn't one. In the SQL example above there are fields and values that aren't in your sample tables, so I don't know exactly what you are trying to achieve here.