Just for fun (and providing the Employee_ID is valid for determining First and Last), you could use Cross Apply to get the First and Last Record so that when the Access SQL has First(FirstName) you just convert it to First.FirstName, etc. keeping your select column output list very close to the original Access SQL.

Thanks Sean for your create/populate script!

SELECT employee.CC

, spaces.space_ID

, SpaceType

, spaces.Space_Number AS Spaces_SpaceNumber

, First.FirstName AS First1

, First.LastName AS Last1

, Last.FirstName AS First2

, Last.LastName AS Last2

FROM Spaces

LEFT JOIN Employee

ON Spaces.Space_ID = Employee.Space_Number

--use cross apply to get the First record in Employee_ID sequence

CROSS APPLY (SELECT *

FROM employee f

WHERE f.Space_Number = Spaces.Space_ID

AND f.Employee_ID = (SELECT MIN(Employee_ID) FROM employee WHERE space_number = Spaces.Space_ID)

) First

--use cross apply to get the Last record in Employee_ID sequence

CROSS APPLY (SELECT *

FROM employee f

WHERE f.Space_Number = Spaces.Space_ID

AND f.Employee_ID = (SELECT MAX(Employee_ID) FROM employee WHERE space_number = Spaces.Space_ID)

) Last

GROUP BY employee.CC

, Spaces.Space_ID

, SpaceType

, spaces.Space_Number

, First.FirstName

, First.LastName

, Last.FirstName

, Last.LastName

-- use the following cases in place of Last.FirstName and Last.LastName, if you need First2 and Last2 to be NULL if there is only one employee:

-- , CASE WHEN First.Employee_ID = Last.Employee_ID THEN NULL ELSE Last.FirstName END

-- , CASE WHEN First.Employee_ID = Last.Employee_ID THEN NULL ELSE Last.LastName END