Query Problem - Use Group By?

[crhodus]

Hi everyone,

I'm having problems with a query.
I've got a table with the following fields in my Employee table:
EmployeeID, FirstName, LastName, SupervisorID

I'm trying to create a query that will pull the following information about all the employees:
FirstName, LastName, {Supervisor LastName}, {Supervisor FirstName}

The supervisor first and last name comes from finding the correct EmployeeId that matches the SupervisorID in the employee table.

Table Data:
EmpID, FirstName, LastName, SupervisorID
111, John, Doe, 222
222, Jane, Doe, 444
333, Carl, Smith, 444
444, Sally, Gates, 555

Desired Query Results:
John Doe, Jane Doe
Jane Doe, Sally Gates
Carl Smith, Sally Gates
Sally Gates, etc.....

Would I need to use a GROUP BY in my statement?

Thanks,
CRhodus

 

[Brianwarnock]

I've not done this but the way I see this having to be tackled is

Query1 select from Table Firstname Lastname supervisorID

query2 joins query1 to table supervisorID to EmployeeID and selects

Query1.Firstname Query1.Lastname Table.Firstname Table.Lastname

Brian

 

[mschwent]

You need to alias the employee table as two different names, so you can specify you are comparing its data to itself. This should work:

SELECT (e.[firstname] & " " & e.[lastname]) AS employee,
(s.firstname & " " & s.lastname) AS supervisor
FROM employee AS e, employee AS s
WHERE e.supervisorid = s.empid
ORDER BY e.[empID];

No need for group by, might be possible with one though, I'm not too sure.

Mike

 

[Brianwarnock]

Well bugger me, if you will excuse the language, seeing Mike's answer I thought I'll give that a go, knocked up a simple test and discovered that you can do it from the query grid, just like that. Access appends _1 to the table name for the second use of the Table.

I'm still learning

Brian

 

[crhodus]

Thanks Mike! That did it.

CRhodus