M
microsoft access
I'm trying to query a table of employee data for a company's reporting
structure. The table has business units for each employee along with
other detail including the hierarchical reporting structure of the
organization.
I'm trying to query the data showing only one instance of each
business unit along with the different reporting structures related to
it. There are instances that have the same business unit but multiple
reporting structures as well as business units that are one-to-one
relationship between bus unit and reporting structure.
Example:
Let say this bus unit has three different reporting structures
assigned.
Table Name is "tblSalesOrg_Current"
Business unit field is "Bus Unit"
Reporting structure fields row data includes position number and
names: "Reports To"(immediate supervisor), "Level 1 Mgr"(like VP),
"Level 2 Mgr"(like Director), "Level 3 Mgr"(Staff Mgr)
Employee field is tracked by position number called "Position"
First instance: (One-to-Many)
Position:Employee X
Bus Unit: 950901335
Level 1 Mgr: Kevin Ross
Level 2 Mgr: Michael Smith
Level 3 Mgr: George Shields
Second Instance: (One-to-Many)
Position: Employee Y
Bus Unit: 950901335
Level 1 Mgr: Larry David
Level 2 Mgr: Sam Leach
Level 3 Mgr: Sean Stephens
Third Instance: (One-to-Many)
Position: Employee Z
Bus Unit: 950901335
Level 1 Mgr: Ralph Cousins
Level 2 Mgr: Mark Lett
Level 3 Mgr: Justin Matthews
Fourth Instance (One-to-One)
Position: Employee A
Bus Unit: 951522109
Level 1 Mgr: Sam Cousins
Level 2 Mgr: Mark Edwards
Level 3 Mgr: John Man
All I need I believe is the bus unit and then reporting structures as
fields. Just having problems thinking through how to achieve this in
one fail swoop. Do you have any recommendations? Anyway to right an
expression to capture what I want? Thanks!
structure. The table has business units for each employee along with
other detail including the hierarchical reporting structure of the
organization.
I'm trying to query the data showing only one instance of each
business unit along with the different reporting structures related to
it. There are instances that have the same business unit but multiple
reporting structures as well as business units that are one-to-one
relationship between bus unit and reporting structure.
Example:
Let say this bus unit has three different reporting structures
assigned.
Table Name is "tblSalesOrg_Current"
Business unit field is "Bus Unit"
Reporting structure fields row data includes position number and
names: "Reports To"(immediate supervisor), "Level 1 Mgr"(like VP),
"Level 2 Mgr"(like Director), "Level 3 Mgr"(Staff Mgr)
Employee field is tracked by position number called "Position"
First instance: (One-to-Many)
Position:Employee X
Bus Unit: 950901335
Level 1 Mgr: Kevin Ross
Level 2 Mgr: Michael Smith
Level 3 Mgr: George Shields
Second Instance: (One-to-Many)
Position: Employee Y
Bus Unit: 950901335
Level 1 Mgr: Larry David
Level 2 Mgr: Sam Leach
Level 3 Mgr: Sean Stephens
Third Instance: (One-to-Many)
Position: Employee Z
Bus Unit: 950901335
Level 1 Mgr: Ralph Cousins
Level 2 Mgr: Mark Lett
Level 3 Mgr: Justin Matthews
Fourth Instance (One-to-One)
Position: Employee A
Bus Unit: 951522109
Level 1 Mgr: Sam Cousins
Level 2 Mgr: Mark Edwards
Level 3 Mgr: John Man
All I need I believe is the bus unit and then reporting structures as
fields. Just having problems thinking through how to achieve this in
one fail swoop. Do you have any recommendations? Anyway to right an
expression to capture what I want? Thanks!