Staffing Table/s - Line Management (1 Viewer)

[Amoff]

Hi All,

Hoping someone might be able to assist or point me in the right direction.

I have setup a table for all employees with a primary key, name and other relevant information

The second table I have set up is for employee line management.

This table currently consist off a
Primary key
Line manager (Linked back to the employees table)
Subordinate/s (Linked back to the employees table)

This process has been working on the forms I have and allows navigation through the organisational structure well.

However now I am unsure on the process of collecting all the data for reporting down the track

How would I write a query that would show information linked to the employee table where there was multiple levels of line management ?
I am wondering if I should be using a different table structure ? as I cannot see how to achieve this.

State Manager	Store Manager 1	Employee 1
		Employee 2
		Employee 3
	Store Manager 2	Employee 4
		Employee 5
		Employee 6	Apprentice 1
			Apprentice 2

So what I am hoping to achieve is when

Selecting State Manager, would bring back all details as above
Selecting store manager 1 would bring information on Store Manager 1, employee 1 & ,2 & 3
Selecting store manager 2 would bring information on Store Manager 3, employee 4 & 5 & 6 and Apprentice 1 & 2

I do hope I have explained myself clearly and that I haven't posted a duplicate question

Any assistance would be greatly appreciated

Regards

Adam

 

[MajP]

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...

www.access-programmers.co.uk

If interested I can help.
If it only ever gets down to three levels, you can show this is a query pretty easily. You still store this in a Hierarchical structure, but you can make the query by bringing the same table in 3 times.

 

[Amoff]

Thank you, MajP.

After having a read of what you have provide it is already making a lot more sense.

I will have a play with what you have given me and see how I go.

Thanks again for the prompt reply

Regards

Adam

 

[Minty]

Bit late to the party here, and @MajP is the hierarchy king here, but if every EmpId has a Manager ID I don't see the need for the Subordinate field, as you can always work out the subordinates?