Found the answer to the missing operator error at:

http://nm1m.blogspot.c om/2007/10/multiple-left-joins-in-ms-access.html

“I had forgotten about this one. In Microsoft Access, if you want to do more than one LEFT JOIN, you have to use parenthesis in the FROM clause. So, for example, instead of just plain old:

SELECT a.columna, b.columnb, c.columnc
FROM tablea AS a LEFT JOIN tableb AS b ON a.id...

I am told from another forum it (my original SQL) works with SQLServer 2005 because 2005 has "With Ties option and ties are implicitly assumed."

It can be made to work in Access by doing a sort on all fields in the view but is very very slow and have been advised to go for joins as you suggested.

However, your code works (Had to make an INNER JOIN where you had JOIN after the LEFT JOIN - hope that makes sense)...

Using SQL Server 2005 I have on a number of occasions, succesfully used the following code to create tables with the last dated results or observations for each Patient. However I am now being asked to do the same in Access 2003 and all the variations I can think of return the error message “At most one record can be returned by this subquery”. Do I have to give up with Access?

SELECT Patient,
(SELECT TOP 1 t1.LabResult1...