Get recursion field as sql server (1 Viewer)

ayamnash

New member
Local time
Today, 01:22
Joined
Sep 23, 2016
Messages
25
I have a table on sql server As shown in the picture
1.png

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...]
l get grandparent , parent , child etc for employee as field named - grandparent,p, c As shown in the picture

3.png

my question is how can i to get same result in ms- access query
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:22
Joined
Feb 19, 2002
Messages
34,462
Access SQL seems to be locked into whatever was available pre-1992. To do recursion, you would need to write VBA to produce the desired recordset or do it the easy way as @cheekybuddha recommended and use a pass-through query.
 
Last edited:

ayamnash

New member
Local time
Today, 01:22
Joined
Sep 23, 2016
Messages
25
(use pass throug query )Do you mean i run ms- Access as a front-end application and then link to tables in a back-end server,
No i have already that .but i want run my database as local not as front end
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:22
Joined
May 7, 2009
Messages
15,050
it can be done, but i used VBA to achieve your result on local table.
table1 holds the data, output table is a Temporary table.
while Query1 is the final output.

but first you need to run addToOutput() function (in Module1).
after you have run the function, open Query1 for the result.
 

Attachments

  • recursion.accdb
    496 KB · Views: 202

cheekybuddha

AWF VIP
Local time
Today, 09:22
Joined
Jul 21, 2014
Messages
824
@arnelgp is correct.

You will have to use VBA to emulate the recursive abilities not present in Access SQL.

I would avoid a temp table, and just use a function that performs the recursive lookup - but performance might [will!] be horrible on larger tables.

With a function called RecurseParents() you can use SQL like:
SQL:
SELECT
  t1.empID,
  t1.parentID,
  t1.employee_name AS Recursion,
  Nz(t2.employee_name, t1.employee_name) AS employee_name,
  RecurseParents(t1.empID) AS grandparent_p_ch,
  IIf(
    t1.employee_name = Nz(t2.employee_name, t1.employee_name),
    t1.empID,
    LEFT(
      RecurseParents(t1.empID),
      IIf(
        InStr(1, RecurseParents(t1.empID), "-") > 0,
        InStr(1, RecurseParents(t1.empID), "-") - 1,
        Len(RecurseParents(t1.empID))
      )
    )
  ) AS Root
FROM Table1 t1
LEFT JOIN Table1 t2
       ON t1.parentID = t2.empID
ORDER BY
  RecurseParents(t1.empID)
;

See Query2 in the attachment, and the code in Module2.

The function is not truly recursive since it does not call itself, but it could easily be re-written that way.

Also note, that due to the way access queries work, the function is called 29 times for the 7 records in the table :oops:😖
 

ayamnash

New member
Local time
Today, 01:22
Joined
Sep 23, 2016
Messages
25
it can be done, but i used VBA to achieve your result on local table.
table1 holds the data, output table is a Temporary table.
while Query1 is the final output.

but first you need to run addToOutput() function (in Module1).
after you have run the function, open Query1 for the result.
But when i change data in table gave me incorrect result see below
Screenshot 2021-07-05 062547.png


@arnelgp is correct.

You will have to use VBA to emulate the recursive abilities not present in Access SQL.

I would avoid a temp table, and just use a function that performs the recursive lookup - but performance might [will!] be horrible on larger tables.

With a function called RecurseParents() you can use SQL like:
SQL:
SELECT
  t1.empID,
  t1.parentID,
  t1.employee_name AS Recursion,
  Nz(t2.employee_name, t1.employee_name) AS employee_name,
  RecurseParents(t1.empID) AS grandparent_p_ch,
  IIf(
    t1.employee_name = Nz(t2.employee_name, t1.employee_name),
    t1.empID,
    LEFT(
      RecurseParents(t1.empID),
      IIf(
        InStr(1, RecurseParents(t1.empID), "-") > 0,
        InStr(1, RecurseParents(t1.empID), "-") - 1,
        Len(RecurseParents(t1.empID))
      )
    )
  ) AS Root
FROM Table1 t1
LEFT JOIN Table1 t2
       ON t1.parentID = t2.empID
ORDER BY
  RecurseParents(t1.empID)
;

See Query2 in the attachment, and the code in Module2.

The function is not truly recursive since it does not call itself, but it could easily be re-written that way.

Also note, that due to the way access queries work, the function is called 29 times for the 7 records in the table :oops:😖
I would like ro see your attempt but where your attachment?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:22
Joined
May 7, 2009
Messages
15,050
try with your data again.
 

Attachments

  • recursion.accdb
    1.2 MB · Views: 207

cheekybuddha

AWF VIP
Local time
Today, 09:22
Joined
Jul 21, 2014
Messages
824
🤪

Sorry, forgot the attachment!
 

Attachments

  • recursion.accdb
    476 KB · Views: 208

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:22
Joined
May 7, 2009
Messages
15,050
its because i am "reversing" the string, here is the fix.

on the cheeky's db, is the recursion column correct? i don't think so.
if you have big dataset, everytime you move to next record on the query,
it will call the function. therefore will slow you down.

while i use temp table, everything is dump on that table and there
is no function call on the query so it is fast to browse the query.
 

Attachments

  • agp_recursion.accdb
    1.2 MB · Views: 205

cheekybuddha

AWF VIP
Local time
Today, 09:22
Joined
Jul 21, 2014
Messages
824
on the cheeky's db, is the recursion column correct? i don't think so.
Haha! I had the columns recursion/employee_name the wrong way round. Thanks for pointing that out 👍

You can adjust Query2 to:
SQL:
SELECT
  t1.empID,
  t1.parentID,
  Nz(t2.employee_name, t1.employee_name) AS Recursion,
  t1.employee_name,
  RecurseParents(t1.empID) AS grandparent_p_ch,
  IIf(
    t1.employee_name = Nz(t2.employee_name, t1.employee_name),
    t1.empID,
    LEFT(
      RecurseParents(t1.empID),
      IIf(
        InStr(1, RecurseParents(t1.empID), "-") > 0,
        InStr(1, RecurseParents(t1.empID), "-") - 1,
        Len(RecurseParents(t1.empID))
      )
    )
  ) AS Root
FROM Table1 t1
LEFT JOIN Table1 t2
       ON t1.parentID = t2.empID
ORDER BY
  RecurseParents(t1.empID)
;
 

ayamnash

New member
Local time
Today, 01:22
Joined
Sep 23, 2016
Messages
25
its because i am "reversing" the string, here is the fix.

on the cheeky's db, is the recursion column correct? i don't think so.
if you have big dataset, everytime you move to next record on the query,
it will call the function. therefore will slow you down.

while i use temp table, everything is dump on that table and there
is no function call on the query so it is fast to browse the query.
I think this is the best solution for a large database
Thank you very much(y)
 

Users who are viewing this thread

Top Bottom