CODEWITHSUNDEEP
sqlms-access
Manas Saha
Manas Saha

Reputation: 1497

SQL query in MS-Access to arrange records in a hierarchy

I have a MS-access database which have the below columns

Userid (primary key)

Username

Userparent (this is nothing but userid of the user who is boss of the current user)

userlevel (this is the position of the current user in the hierarchy)

There are only 3 levels. for example, if Tom and Jerry works for Bob, and bob and bill works for Steve, then the data in the table will look like this:

Userid ,Username,Userparent ,userLevel(these are col headers)

1, Steve, 0, 1

2, Bob, 1, 2

3, Tom, 2, 3

4, Jerry, 2, 3

5, Bill, 1, 2

I need to use a SQL query in MS-Access 2003 to arrange the data in a new table which will have 3 columns. Level1, level2, level3. and the sub level employees will appear immediate below the higher level. Like this.

Level1,Level2,Level3 (these are col headers)

Steve,null,null

null,Bob,null

null,Null,Tom

null,Null,Jerry

null,Bill,null

Since recursive queries are not supported in access, I am stuck. Could you please help me out? thanks a lot in advance

UPDATE

After a hour of trying, The closest I could go is by using this SQL query:

Select y.Level1, x.Level2, x.Level3
FROM
(Select ID, Empname as Level1 from Table1 where level=1)y
right outer join
(select b.FirstLevelID, b.Level2, a.Level3 from
    (select ParentID as SecondLevelID, Empname as Level3 from Table1 where level = 3)a
    right outer Join
    (select ParentID as FirstLevelID,  ID, Empname as Level2 from Table1 where level = 2) b
    on a.SecondLevelID = b.ID)x
ON y.ID = x.FirstLevelID

It shows result this way,

Level1, level2, level3 (these are headers)

Steve,bob,jerry

Steve,bob,tom

Steve,Bill, null

This establishes the relations correctly, but could have been better if I could remove the re-appearance of Level 1 and Level 2 names multiple times. Can anyone modify the query to achieve that please?

Upvotes: 0

Views: 1955

Answers (1)

MikeAinOz
MikeAinOz

Reputation: 128

Can I suggest that you migrate to SQL Server Express, which is zero cost? A Common Table Expression (CTE) may well solve your problem as it can recurse down a tree.

Upvotes: 0

Related Questions