I have a table on sql server As shown in the picture
and when run this sql statement
l get grandparent , parent , child etc for employee as field named - grandparent,p, c As shown in the picture
my question is how can i to get same result in ms- access query
and when run this sql statement
SQL:
with cte as (select h1.empid,
h1.parentid,
h1.employee_name [recursion],
employee_name [employee name],
cast(h1.id as varchar (max)) [grandparemt.p.ch...]
from mie h1
where h1.parentid=0
union all select h2.empid,
h2.parentid,
c.[employee name],
employee_name [self description],
c.[grandparemt.p.ch...] +'-' +cast (h2.id as varchar (10)) [grandparemt.p.ch...]
from mie h2
inner join cte c on h2.parentid=c.empid)
select *
from cte cross apply
(select SUBSTRING([grandparemt.p.ch...],1,charindex('-',[grandparemt.p.ch...]+ '-')-1 )) c(root)
order by [grandparemt.p.ch...]
my question is how can i to get same result in ms- access query