Link to home
Create AccountLog in
Avatar of chris_desborough
chris_desboroughFlag for Australia

asked on

SQL query with subquery does not run

This is the SQL from my Access query which just does not complete but neither does it error.
I am trying to get the 5 most recent episodes (with test T122) for all patients.

SELECT Patients.UnitNumber, Episodes.EpisodeNo, EpisodeTests.TestSet, Episodes.DateCreated, Episodes.RefDoc, Patients.Name, Patients.Add1, Patients.Add2, Patients.Postcode, Patients.DOB, Patients.Sex, Results.TestCode, Results.ResultValue
FROM ((Patients INNER JOIN Episodes ON Patients.UnitNumber = Episodes.UnitNumber) INNER JOIN EpisodeTests ON Episodes.EpisodeNo = EpisodeTests.EpisodeNo) INNER JOIN (TestSetsCodes INNER JOIN Results ON TestSetsCodes.TestCode = Results.TestCode) ON EpisodeTests.TestSet = TestSetsCodes.TestSet
WHERE (((EpisodeTests.TestSet)="T122") AND ((Episodes.DateCreated) In (SELECT TOP 5 E.DateCreated FROM (Episodes as E INNER JOIN EpisodeTests as T ON E.EpisodeNo = T.EpisodeNo)  WHERE (((T.TestSet)="T122") AND ((E.UnitNumber)=Episodes.UnitNumber)) ORDER BY E.DateCreated DESC)));
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of chris_desborough

ASKER

I have not actually tried that.  I will put in a single patient UnitNumber and try it when I have the large SQL databases available in another hour.  Will let you know how it goes. Thanks, Chris.
No subquery would not run either. Had to go back to the drawing board and re-design. Thanks for pointing me in the right direction.