Multilevel query in ms access

Question

0.00/5 (No votes)

See more:

Hi All,

I am trying to write a query in ms access 2013 which will output subordinates and subordinates of subordinates and so on for a given manager. Need some hint as I am unable to think of any solution. I am stuck at first level only.
Any help or hint or work around will be of great help. I am using ms access 2013 with c#.net.

table is in the following form. E.g for manager sm1 output will be m1,m2,e1,e2,e3 and for manager m1 output will be e1, e2.

C#

emp	mangr
e1	m1
e2	m1
e3	m2
m1	sm1
m2	sm1
m3	sm2
e4	m3

What I have tried:

this is a simple query to get first level subordinates:

SELECT emp from test where mangr='sm1';

But I am not able to go beyond that. I also need emp from test whose mangr is output of this query.

Posted 12-Jul-16 7:53am

planetz

Updated 12-Jul-16 11:40am

Add a Solution

2 solutions

Solution 1

This can be done is a real SQL engine using a Common Table Expression (CTE).

Access does not have any support for CTEs. I don't know of a way to do this in a single query in Access. You'd have to use VBA code to do it.

Posted 12-Jul-16 9:23am

Dave Kreskowiak

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-07-12T11:40:00

Solution 2

You need to use subquery together with IN clause. I'd try something like that:

SQL

SELECT emp
FROM Table1 
WHERE mangr IN
    (
     SELECT emp
     FROM Table1
     WHERE mangr='sm1'
     );

For further details, please see: Examples of query criteria - Access[^]

Posted 12-Jul-16 11:40am

Maciej Los