Access only runs on one processor?

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
FROM Patient
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.
Scotto123Asked:
Who is Participating?
 
Brian HarringtonConnect With a Mentor IT ManagerCommented:
Actually that doesn't matter. Access does not support multi threading.  Consider using SQL Express which does.
0
 
Brian HarringtonIT ManagerCommented:
What version are you using?
0
 
Scotto123Author Commented:
I have Access 2010.  I could run the data in SQL Server Mgt Studio.  I am anayzing data and want to be able to do it easier without having to build SQL tables and such.  Maybe I should change my analysis style.
0
 
Dale FyeCommented:
It's your nested select statements that are causing the problem.

Try creating a query like this:

SELECT N.PatientID, N.AdmitDateTime, Max(P.AdmitDateTime) as PrevAdmit, Max(P.DischargeDateTime) as PrevDischarge
FROM Patients N
LEFT JOIN Patients P
ON N.PatientID = P.PatientID
AND P.DischargeDateTime < N.AdmitDateTime
GROUP BY N.PatientID, N.AdmitDateTime

That should give you every PatientID, Admission, and the patients most recent previous admission and discharge.  If that works, then use that as a subquery and join it to your Patients table on PatientID and AdmitDateTime to get the other fields you need from the Patients table.

But do you really need every patient/admit combination?  If not you could limit the results of this query significantly by adding a WHERE clause on the N.AdmitDateTime field.  Or by minimizing the number of records in the N part of the query by using something like:

SELECT N.PatientID, N.AdmitDateTime, Max(P.AdmitDateTime) as PrevAdmit, Max(P.DischargeDateTime) as PrevDischarge
FROM (SELECT PatientID, MAX(AdmitDateTime) as MostRecentVisit FROM Patients) as N
LEFT JOIN Patients P
ON N.PatientID = P.PatientID
AND P.DischargeDateTime < N.MostRecentVisit
GROUP BY N.PatientID, N.AdmitDateTime

By using the subquery in this, you limit N to one record per patient, and the resulting recordset, with the PrevAdmit and PrevDischarge will only contain one record as well.
0
 
Scotto123Author Commented:
You anwered my question harrington.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.