chris_desborough
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.U nitNumber) ) ORDER BY E.DateCreated DESC)));
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)="
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.
ASKER