Segregarting results of a join into two fields

[Al_Foote]

I have a two tables -- one has the fields "Company Name", "Partner" and
"Manager". The latter two fields are numerical and hold the ID for the
individuals (the key in my second table).

The second table has detail information for the Partners and Managers.

I want to run a query that pulls the company names and the names of the
partners and names of the managers and distributes the partners and managers
into separate columns (e.g., Company name, Partner last name, Manager last
name).

Obviously a regular Left Join puts all the names in one column and
duplicates the company names. Any way to accomplish this?

 

[Al_Foote]

Oh -- and just to make it more fun, I can't use a staff class field, as some
managers have become partners, but are still reflected as managers on these
historical projects.

 

[karl dewey]

Try this --
SELECT [Company Table].[Company Name], People.LName AS Partners,
People_1.LName AS Managers
FROM ([Company Table] LEFT JOIN People ON [Company Table].Partner =
People.PeopleID) LEFT JOIN People AS People_1 ON [Company Table].Manager =
People_1.PeopleID;

 

[Al_Foote]

Mr. Dewey -- you are a beautiful man! Thank you.

Al

Try this --
SELECT [Company Table].[Company Name], People.LName AS Partners,
People_1.LName AS Managers
FROM ([Company Table] LEFT JOIN People ON [Company Table].Partner =
People.PeopleID) LEFT JOIN People AS People_1 ON [Company Table].Manager =
People_1.PeopleID;

--
KARL DEWEY
Build a little - Test a little

Oh -- and just to make it more fun, I can't use a staff class field, as some
managers have become partners, but are still reflected as managers on these
historical projects.