I think I need a nested query but here's the problem. I promise i've
exerted my meager brain power on this problem & ask this as a last
resot!

3 Tables
PatientTable
   PatActive, PatID
ProfileTable
   PatID, CodeID, OtherInfo, MedicineID
MedicineTable
   MedicineID MedicineInfo

I need a return from Table2 Info including the detailed child data from
Table3 [OtherInfo] where patient is active, however i only want rows
from Profile table where the element CodeID is MAX for each patient.

subquery is fine
SELECT MAX(ProfileTab.CodeID) AS CodeID , patienttable.patsurname FROM
(profileTable INNER JOIN patienttable ON profiletable.PatID=
patienttable.PatID) GROUP BY patienttable.patsurname HAVING
(patienttable.patactive = 1)

I had to add the Group by statement when I tried to add this into
another query.

I dont have direct access to the DB so cannot use stored queries.