C
Carma
I have a employee database(more of a spreadsheet currently but...) which
contains thousands of employee records in one table. I have created a form
based upon a query to isolate all supervisor's(by pos#) which permits the
user to select one supervisor with the intent that they can then click a
button to produce a report showing the hierarchy of all positions below the
one selected. I thought this would be very simple for me to do but it has
turned out to be very troublesome and maybe someone out there with more
expertise has an easy solution.
Problem: How can I design a report to illustrate this hierarchy
Considerations:
-There can be up to 12 different levels in the hierarchy.
Very simplistic Table Example:
Pos# Description Pay level Supervisor Pos#
001 Admin CR04 002
002 Sr. Admin CR05 003
003 Manager FI01 004
004 Minister DM null (head of organization so
field is null)
005 manager2 FI01 004
006 supervisor CR05 005
007 sr analyst FI01 004
Assume user wants hierarchy for all positions under DM.
Ideal Report would be:
L1 L2 L3 L4 Description
004 Minister
005 Manager2
006 Supervisor
007 Sr Analyst
003 Manager
002 Sr. Admin
001 Admin
So I created a query which would identify the complete hierarchy and the
results look like this:
L1 L1Desc L2 L2Desc L3 L3 Desc L4 L4 Desc
004 Minister 005 Manager2 006 Supervisor
004 Minister 007 Sr Analyst
004 Minister 003 Manager 002 Sr Admin 001 Admin
So I created a report with group headers for L1, L2, L3, L4 and so on. This
achieved my desire to have steps to isolate and identify the complete
information for each position number but it came with one problem... It
created empty rows because the report design dictates a separate line for
each grouping.
So the report would look like this instead of above version:
L1 L2 L3 L4
004 Minister
005 Manager2
006 Supervisor
007 Sr Analyst
003 Manager
002 Sr. Admin
001 Admin
This may not look that bad but when you have 10 or more levels the empty row
spacing really starts to add up. Is there a way to stop this?
I'm using MS Access 2002.
contains thousands of employee records in one table. I have created a form
based upon a query to isolate all supervisor's(by pos#) which permits the
user to select one supervisor with the intent that they can then click a
button to produce a report showing the hierarchy of all positions below the
one selected. I thought this would be very simple for me to do but it has
turned out to be very troublesome and maybe someone out there with more
expertise has an easy solution.
Problem: How can I design a report to illustrate this hierarchy
Considerations:
-There can be up to 12 different levels in the hierarchy.
Very simplistic Table Example:
Pos# Description Pay level Supervisor Pos#
001 Admin CR04 002
002 Sr. Admin CR05 003
003 Manager FI01 004
004 Minister DM null (head of organization so
field is null)
005 manager2 FI01 004
006 supervisor CR05 005
007 sr analyst FI01 004
Assume user wants hierarchy for all positions under DM.
Ideal Report would be:
L1 L2 L3 L4 Description
004 Minister
005 Manager2
006 Supervisor
007 Sr Analyst
003 Manager
002 Sr. Admin
001 Admin
So I created a query which would identify the complete hierarchy and the
results look like this:
L1 L1Desc L2 L2Desc L3 L3 Desc L4 L4 Desc
004 Minister 005 Manager2 006 Supervisor
004 Minister 007 Sr Analyst
004 Minister 003 Manager 002 Sr Admin 001 Admin
So I created a report with group headers for L1, L2, L3, L4 and so on. This
achieved my desire to have steps to isolate and identify the complete
information for each position number but it came with one problem... It
created empty rows because the report design dictates a separate line for
each grouping.
So the report would look like this instead of above version:
L1 L2 L3 L4
004 Minister
005 Manager2
006 Supervisor
007 Sr Analyst
003 Manager
002 Sr. Admin
001 Admin
This may not look that bad but when you have 10 or more levels the empty row
spacing really starts to add up. Is there a way to stop this?
I'm using MS Access 2002.