I was running this query today and my PC was freaking out!
SELECT Patient.PatientID, Patient.VisitID, Patient.AdmitDateTime, (select max([AdmitDateTime]) from Patient P where P.PatientID=Patient.PatientID and P.AdmitDateTime<Patient.AdmitDateTime) AS PrevAdmissionDate, DateDiff("d",[PrevAdmissionDate],[AdmitDateTime]) AS AdmissonDiff, Patient.DischargeDateTime,(select max([DischargeDateTime]) from Patient P where P.PatientID=Patient.PatientID and P.DischargeDateTime<Patient.DischargeDateTime) AS PrevDischargeDate, DateDiff("d",[PrevDischargeDate],[DischargeDateTime]) AS DischargeDiff
ORDER BY Patient.PatientID, Patient.AdmitDateTime;
The query ran for 30 minutes.
The CPU was maxed out and the RAM was all used up. I learned that Access only runs on one of the 4 CPUs I have. Is this right? Seems crazy that my PC can handle so much and it doesn't utilize the full system. Please let me know what I can do to speed up queries like this.