HR Report

[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.

 

[Carma via AccessMonster.com]

Oh come on someone has to be able to help me with this or at the very least
tell me if its possible or not.

 

[John Spencer]

I may be misunderstanding, but did you set the Can Shrink property to true
for each section and all of the controls within each section?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Carma via AccessMonster.com]

I may be misunderstanding, but did you set the Can Shrink property to true
for each section and all of the controls within each section?

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

[quoted text clipped - 69 lines]

I'm using MS Access 2002.

I don't think you misunderstood anything. No I hadn't had that property set
but when I did it worked beautifully. Thank you very much John.