Inserting last records into another table

[PeteM]

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
FROM View1 AS t1
WHERE t1.Patient = b.Patient
ORDER BY t1.ResultDate DESC) AS LabResult1,
(SELECT TOP 1 t1.LabResult2
FROM View2 AS t1
WHERE t1. Patient = b.Patient
ORDER BY t1.ResultDate DESC) AS LabResultn,
(SELECT TOP 1 t1.LabResultn
FROM Viewn AS t1
WHERE t1. Patient = b.Patient
ORDER BY t1.ResultDate DESC) AS LabResultn
INTO LastLabResult
FROM (SELECT Patient
FROM PatientDetail) b


View code
create view View1
as
select Patient, ResultDate, LabResult1
from LabResultsTable
where LabResult1 is not null
group by Patient, ResultDate, LabResult1 -- to remove duplicates
(not all tests carried out at the same time so each result has a separate view)

[Delerna]

Hi Pete....welcome

Short answer ...... No you don't have to give up on access.

I must say I don't understand how that query worked in SQLSever 2005, I have never used 2005 but it looks very strange to my SQLServer 2000 experience.

As a first attempt I would write it something like this for access

INSERT INTO LastLabResult
SELECT a.Patient,LabResult1,LabResult2,LabResultn
FROM PatientDetail a
left join
(   Select Patient,LabResult1
    FROM
    (   select Patient,max(ResultDate) as ResDte from View1 group by Patient
    ) a
    join View1 b on a.Patient=b.Patient and a.ResDte=b.ResultDate
)T1 on a.Patient=T1.Patient
left join
(   Select Patient,LabResult2
    FROM
    (   select Patient,max(ResultDate) as ResDte from View2 group by Patient
    ) a
    join View1 b on a.Patient=b.Patient and a.ResDte=b.ResultDate
)T2 on a.Patient=T2.Patient
left join
(   Select Patient,LabResultn
    FROM
    (   select Patient,max(ResultDate) as ResDte from View3 group by Patient
    ) a
    join View1 b on a.Patient=b.Patient and a.ResDte=b.ResultDate
)T3 on a.Patient=T3.Patient
 

I hope I understand what you are doing with your orriginal query. I think I do.
I also Hope the above works for you as I could not test it as I don't have the tables/views or data.

Good luck, you should be able to achieve it one way or another

[PeteM]

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) for one LabTest but wont extend with further a LEFT JOIN - syntax error missing operator?

Pete

[PeteM]

Found the answer to the missing operator error at:

http://nm1m.blogspot.com/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 = b.id LEFT JOIN tablec AS c ON a.id = c.id

you would have to do the following:

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

Otherwise, you get a "Missing Operator" error.”

[Delerna]

AHA, that explains why access puts all those brackets everywhere when you use its query wizard. Never worried about it though because at work I always use SQLServer or DB2 for the backend and all queries are as Views,Stored procs and UDF's in SQL Server. At most I use access as the front end to those! Don't even do that much any more because we now use the intranet for our WEB based front end (easier delivery of modifications/fixes/etc to multiple and varied users).

But now, thanks to your post, I know why they are there. Probably explains why some of my queries refuse to work in access :)