Setting up a Parameter Querry

[Don Bryant]

How would I set up a querry that would allow me to look at a line of people
even if it was 6 levels deep from upper management to lower to employees
where I am using a table with all employees from field supervisor ID to field
employee ID and I need to type in one name and get the entire line of
employees and lower management that reports to that manager at what ever
level the manager resides it varies to some managers having 6 levels of tier
structure. Using Access 2003

 

[scubadiver]

Hello,

Your message doesn't make any sense (to me at least). Can you be clearer
with your table structure and set up?

thanks!

 

[Larry Linson]

How would I set up a querry that would allow me to look at a line of
people
even if it was 6 levels deep from upper management to lower to employees
where I am using a table with all employees from field supervisor ID to
field
employee ID and I need to type in one name and get the entire line of
employees and lower management that reports to that manager at what ever
level the manager resides it varies to some managers having 6 levels of
tier
structure. Using Access 2003

Assuming an employee has one manager and a manager may have many employees
and that each manager is in turn, the employee of a higher level manager, up
to the CEO. You have one table for employees that includes managers and
non-managers, with a foreign key field to the person's manager's record in
the same table, you use a self-join.

That is, you include the table as many times as you have levels you want to
look at. Join on the Manager ID FK field, and on each join, click to
highlight it, then right click to set the join properties, and choose "All
the records from table <the one on the employee "many" side of the join> and
only those that match in table <the (manager) one on the many side". I warn
that this will not work if you leave the default join property of "only
those records that match".

If that's not the data structure you have, or you have problems, post back
with clarification.

Larry Linson
Microsoft Access MVP

 

[Don Bryant]

Thanks Scubadive maybe this will help some.
This is my table for example, I creat another table and apply it for time,
payroll, or anything. I have the table match the cwsid of the two, then
create this table connecting supervisor to employee ID and do this 5times to
get to the top level.
A manager at any level may want a report to see the status of all those
reporting to him. How do I set it up and where do I apply a manger name to
get all of his people?


ID CWSID Name Employee ID Supervisor Role Active
1 man Michael Andrew Newman 1 President y
2 wom William O'Mellon 2 1 VP Sales y
3 gor George O. Russell 3 1 VP Operations y
4 mon Mitchel O. Neville 4 3 Mgr Finance y
5 hor Horace O. Richards 5 2 Mgr Marketing y
6 dog Daniel O. Goody 6 1 VP HR y
7 cat Carl A. Timber 7 3 Mgr Production y
8 bir Betty Irene Roberts 8 7 Quality Supv y
9 sna Susan N. Anderson 9 7 Production Supv y
10 wor William O. Ringer 10 9 Team lead y
11 ant Albert N. Thomas 11 10 Employee y
12 ter Teresa E. Richmond 12 5 Office Mgr y
13 scr Shelia C Ruddy 13 12 Employee y
14 cat1 Cliff A Thompson 14 8 Employee y
15 sna1 Sam N Akers 15 10 Employee y
16 gru George R Urnicus 16 10 Employee y
17 fal Fred A Little 17 4 Accountant y
18 nel Nancy E Leary 18 6 Clerk y

 

[scubadiver]

I'm still not sure what you mean by "5 times". Are you saying you have five
different tables depending on seniority?

 

[Don Bryant]

No, just one table connected supervisor id to employee id from one table to
another and I have done that 5 times to establish the level of the
management. I need to be able to enter a name of a manager and no matter how
many levels there may be below that manager the manager needs to wee all the
people who report to him and below.