Segregarting results of a join into two fields

  • Thread starter Thread starter Al_Foote
  • Start date Start date
A

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?
 
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.
 
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;
 
Mr. Dewey -- you are a beautiful man! Thank you.

Al

karl dewey said:
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


Al_Foote said:
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.
Click to expand...
Click to expand...
 
Back
Top