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!

microsoft access wrote:

I don't understand what your final data should look like. I recommend
you look at the words Disctinct and GroupBy. Also, there is no reason
to not create subqueries if needed. You can then link these subqueries
to the main table if needed.