Management structure VBA and SQL

[Laik2013]

I have written a macro which shows management structure in my company from the Top manager to simple employees.

The database Table looks like this that: I have one column Emplid which includes all employees in the company (Top manager, management and simple employees). Next columns are Last Name, First name of the employees. One column with Supervisor ID (Managers) which each employee has. Next column is HR Status where I have a information if a employee is still active or on Leave and Absence or International Assignment and Terminated or deceased.

I want that my macro will show the whole structure but only employees who have the status Active and Leave on Absence and International Assignment.

The Terminated and deceased shouldn't be in the extract.

Like here: Top manager--> Only Active Directors ---> Only active managers ---> Only active team leaders ---> only active Supervisors --> Only active employees.

The issue is that I want to use a SQL query and I don't know how to bite this.

I'm using a Excel Workbook as a Data Base.

I have created such macro but I'm weak in SQL and I don't know how to write good the query to get what I want.

Thank you for your help!

Please Login or Register  to view this content.

[Kyle123]

You need to add more where clauses, right now you are only checking L1, you need to also check L2, L3, L4, L5, L6, L7

[Laik2013]

Hi Kyle123

I have changed the query to:

Please Login or Register  to view this content.

But now I'm getting 0 result?

Thank you for your help,

[Kyle123]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[Laik2013]

Hi Kyle123,

I have removed the other posts.

Regards,

[Kyle123]

Are you sure that you should be receiving results for the given manager? What happens if you remove the manager id from the where clause

At a guess you also need an or clause so as not to exclude nulls

We really need to see some data

[Laik2013]

Hi Kyle123

Here a example file of my data base.

In the last row I have made a mistake the top manager is reporting to ohter top manager but not included as employee.

So the Top manager will have as Supervisor Top Manager VIP

Regards,

[Kyle123]

Based on the data you have given me, which one does not perform as expected?

[Laik2013]

Hi Kyle123

The code works good when I include in the Where criteria only for the L1 the filter (Active employees), when I spread on each level the filter (like in the second query example) I got nothing.

I have checked the query from scratch from first Level to last and I have noticed that each time when I add additional level and run the macro I'm getting les data to be showed.

[Kyle123]

Again, which Manager Id does not work as expected? I am trying to replicate your issue

[Laik2013]

The Top VIP Manager ID

[Kyle123]

"Top VIP Manager ID" Isn't in the List you gave me

[Laik2013]

Hi Kyle,

In the attachment post I wrote that in the last Row The Top Manager reports to Top VIP manager outside of the country unit.

In the last row I have made a mistake the top manager is reporting to ohter top manager but not included as employee.

So the Top manager will have as Supervisor Top Manager VIP

Regards,

[Laik2013]

Hi Kyle,

In the attachment post I wrote that in the last Row The Top Manager reports to Top VIP manager outside of the country unit.

In the last row I have made a mistake the top manager is reporting to ohter top manager but not included as employee.

So the Top manager will have as Supervisor Top Manager VIP

Regards,

[Kyle123]

Then it doesn't work as you're searching for a supervisor ID that doesn't exist. I cannot replicate an issue unless you give me all the information - Exactly how am I supposed to help you if you do not give the required info and use vague statements like

In the attachment post I wrote that in the last Row The Top Manager reports to Top VIP manager outside of the country unit.

What does that even mean?

Please give me an example that doesn't work as expected that's in the data you provide, with all the best intentions I can't come up with fictional scenarios that may or may not work

[Laik2013]

Hi Kyle123,

From the begining

Here please find a new example base with over 6000 rows.

The Top Manager id is 00002815

When you run the query:

Please Login or Register  to view this content.

you should get the structure but it doesn't work.

Thank you for your help,