Employee HR Database (Employee Hierarchy). (1 Viewer)

A

andmunn

Registered User.

Local time

Today, 00:51

Joined

Mar 31, 2009

Messages

195

• Jun 20, 2011

• #1

Hello All,

I'm having trouble trying to visualize how to structure my tables for an emplyoee HR database.

I basically want to be able to print "maps" of employee hierercy.. For example:

Level 1:
Manager 1

Level 2:
Manager 1 has the following employees:
Team Leader 1,2,3, and Admin Assistant

Level 3:
Team leader 1,2,3 each have a team of 6 employee's to them.

How would i code such in a database? I want to be able to pritn a report, for example, such that i could select team Leader 1, and it would print all employee's reporting to Team leader 1.

Or, i can print a report for "manager 1", and it would print a report for everyone under Level 2.

Suggestions?
Andrew.

 

HiTechCoach

Well-known member

Local time

Today, 02:51

Joined

Mar 6, 2006

Messages

4,357

• Jun 21, 2011

• #2

Everyone needs to be in the same table.

See if this example helps: Access Treeview Example

 

Last edited: Mar 15, 2016

A

andmunn

Registered User.

Local time

Today, 00:51

Joined

Mar 31, 2009

Messages

195

• Jun 21, 2011

• #3

Thanks for the help.

I tried downloading the file and i get this message "The document must be published before downloading. ".

Advice?
Andrew.

 

jdraw

Super Moderator

Staff member

Local time

Today, 03:51

Joined

Jan 23, 2006

Messages

15,379

• Jun 21, 2011

• #4

I tried downloading the treeview Sample and the never did get to the Treeview, but was taken to other download offerings. I never did see the Treeview Sample.
????

 

HiTechCoach

Well-known member

Local time

Today, 02:51

Joined

Mar 6, 2006

Messages

4,357

• Jun 21, 2011

• #5

Andrew,

Sorry about that. I have it published now. It is ready for you to download.

Boyd Trimmell aka HiTechCoach
Microsoft MVP - Access Expert

 

jdraw

Super Moderator

Staff member

Local time

Today, 03:51

Joined

Jan 23, 2006

Messages

15,379

• Jun 21, 2011

• #6

andmunn,

Boyd's structure for the staff table will do exactly what you need.

 

A

andmunn

Registered User.

Local time

Today, 00:51

Joined

Mar 31, 2009

Messages

195

• Jun 21, 2011

• #7

Thanks!

That table structure (tblstaff) looks like exaCtly what i need.

My question, how do i write a query such that (for example):

tblstaff
ID / Name / Reports To

1 / John Doe / 4
2 / Bill Smith / 1
3 / Jane Orielly / 2
4 / Boss / <blank>

If i wanted to write a query to show everyone reporting to boss, how would i write it?

Results would be something like:

Boss > John Doe > Bill Smith > Jane Orielly

Andrew.

 

HiTechCoach

Well-known member

Local time

Today, 02:51

Joined

Mar 6, 2006

Messages

4,357

• Jun 21, 2011

• #8

See:

Recursive Joins to Query Data Hierarchies in Microsoft Access


You could also write VBA User Defined Function (UDF) to return the list as a string.

 

H

hemant_sable

New member

Local time

Today, 13:21

Joined

Jan 30, 2014

Messages

2

• Jan 30, 2014

• #9

I am not able to open the above mention'access treeview example'. It is giving me forbidden error. Could you please share the example in different format or via mail

 

HiTechCoach

Well-known member

Local time

Today, 02:51

Joined

Mar 6, 2006

Messages

4,357

• Jan 31, 2014

• #10

hemant_sable said:

I am not able to open the above mention'access treeview example'. It is giving me forbidden error. Could you please share the example in different format or via mail

Click to expand...

Try the link now. It should work.

 

H

hemant_sable

New member

Local time

Today, 13:21

Joined

Jan 30, 2014

Messages

2

• Feb 3, 2014

• #11

As per andmunn posted on 06-22-2011 12:01 AM, Can you help us to code the hierarchy model or the query as per the below example:
1 / John Doe / 4
2 / Bill Smith / 1
3 / Jane Orielly / 2
4 / Boss / <blank>

Many Thanks.