I am trying to write a report that displays a hierarchy such as:
Director, Manager, Employee
I want to suppress the repeating Director, Manager for the employees.
This is my code:
sSQL = "SELECT FY99Info.MDName, FY99Info.MgrName, FY99Info.firstname,
FY99Info.lastname, FY99Info.grade
FROM FY99Info
WHERE left(FY99Info.orgcode,5) = '" & left(session("MOrg"),5) & "' and
FY99Info.mgmtlevel < 4
ORDER by FY99Info.MDName, FY99Info.orgcode, FY99Info.mgmtlevel desc "
This is the SQL table layout (FY99Info).
Emp# Org Director Manager Employee Mgmtlevel
1 10336088 Brian Smith Michael Smart Bill Mitchell 0
2 10336088 Brian Smith Michael Smart Heidi Rainey 0
3 10336088 Brian Smith Michael Smart Ponita Asnor 2
4 10336088 Brian Smith Michael Smart Marcus Jones 0
5 10607655 Rick Doe Glenn Thomas Helen Kelley 0
6 10607655 Rick Doe Glenn Thomas Gaston Knight 2
7 10607655 Rick Doe Helen Black Prentis Parker 0
8 10607655 Rick Doe Helen Black Billie Spike 0
9 10739900 Jason Smart Tim Snow Joe Monty 3
10 10739900 Jason Smart Tim Snow Randi Bull 3
11 10739900 Jason Smart Tim Snow Misty Wonton 2
This is how I want it to display on the report
MD Manager Employee
Brian Smith Michael Smart Bill Mitchell
Heidi Rainey
Ponita Asnor
Marcus Jones
Rick Doe Glenn Thomas Helen Kelley
Gaston Knight
Helen Black Prentis Parker
Billie Spike
Jason Smart Tim Snow Joe Monty
Randi Bull
Misty Wonton
