Challenge with select query to limit records

[foxtrot123]

I have a table that tracks changes made to patients' hearing aids (DeviceID) over the course of several doctor visits. In the example below, the patient was given two hearing aids (DeviceID 4 & 5 were "Added") at Visit 1. On Visit 2, DeviceID 5 was repositioned, on Visit 3 it was recalibrated, and on Visit 4 it was removed.

PatientID  VisitID DeviceID Procedure
----------------------------------------
1          1       4        Added
1          1       5        Added
----------------------------------------
1          2       5        Repositioned
----------------------------------------   
1          3       5        Recalibrated
----------------------------------------
1          4       5        Removed

I am trying to write a query that will select only devices that have not been "Removed." In this case, the query should return only one record (the first one, for DeviceID 4).

Any suggestions? I obviously can't just use the criteria "<> Removed" because this would still include the records for Device 5 when it was Added, Repositioned, and Recalibrated.

 

[Isskint]

qryRemoved: Identify deviceID's that have been removed. In the criteria under "Procedure"="Removed"

qryNotRemoved: Calculated field Dcount("DeviceID","qryRemoved","DeviceID =" & DeviceID) in the criteria put =0

 

[foxtrot123]

qryRemoved: Identify deviceID's that have been removed. In the criteria under "Procedure"="Removed"

qryNotRemoved: Calculated field Dcount("DeviceID","qryRemoved","DeviceID =" & DeviceID) in the criteria put =0

Just wanted to say this worked, but there's a bit of a performance issue. tblVisitsDevices has about 4,000 records. qryRemoved returns 1700 records, and qryNotRemoved returns 650. qryNotRemoved takes about 30+ seconds to run. Things will get worse over time as more records get returned. Any suggestions about how to speed this up? Thanks.

qryRemoved:

SELECT tblVisitsDevices.DeviceID, tblVisitsDevices.Procedure
FROM tblVisitsDevices
WHERE (((tblVisitsDevices.Procedure)="Removed"));

qryNotRemoved:

SELECT DISTINCT tblVisitsDevices.DeviceID, DCount("DeviceID","qryRemoved","DeviceID =" & [DeviceID]) AS NotRemoved
FROM tblVisitsDevices
WHERE (((DCount("DeviceID","qryRemoved","DeviceID =" & [DeviceID]))=0));

 

[plog]

First, I'm catching a whiff of an improperly structured database. Do you have a Visit or Device table? If so, then your database isn't properly structured--the PatientID most likely shouldn't be in this table if either of those tables exist in your database.

Second, here's another way to skin this cat without using a DCount call.

NotRemoved_sub

SELECT tblVisitsDevices.PatientID, tblVisitsDevices.DeviceID
FROM tblVisitsDevices
GROUP BY tblVisitsDevices.PatientID, tblVisitsDevices.DeviceID, tblVisitsDevices.Procedure
HAVING (((tblVisitsDevices.Procedure)="Removed"));

NotRemoved

SELECT tblVisitsDevices.PatientID, tblVisitsDevices.DeviceID
FROM tblVisitsDevices LEFT JOIN NotRemoved_sub ON (tblVisitsDevices.DeviceID = NotRemoved_sub.DeviceID) AND (tblVisitsDevices.PatientID = NotRemoved_sub.PatientID)
WHERE (((NotRemoved_sub.PatientID) Is Not Null))
GROUP BY tblVisitsDevices.PatientID, tblVisitsDevices.DeviceID;

 

[foxtrot123]

First, I'm catching a whiff of an improperly structured database. Do you have a Visit or Device table? If so, then your database isn't properly structured--the PatientID most likely shouldn't be in this table if either of those tables exist in your database.

You're correct. PatientID is not necessary in that table.

Second, here's another way to skin this cat without using a DCount call.

NotRemoved_sub

SELECT tblVisitsDevices.PatientID, tblVisitsDevices.DeviceID
FROM tblVisitsDevices
GROUP BY tblVisitsDevices.PatientID, tblVisitsDevices.DeviceID, tblVisitsDevices.Procedure
HAVING (((tblVisitsDevices.Procedure)="Removed"));

NotRemoved

SELECT tblVisitsDevices.PatientID, tblVisitsDevices.DeviceID
FROM tblVisitsDevices LEFT JOIN NotRemoved_sub ON (tblVisitsDevices.DeviceID = NotRemoved_sub.DeviceID) AND (tblVisitsDevices.PatientID = NotRemoved_sub.PatientID)
WHERE (((NotRemoved_sub.PatientID) Is Not Null))
GROUP BY tblVisitsDevices.PatientID, tblVisitsDevices.DeviceID;

These worked great. (I adjusted them a bit so they don't use PatientID.) The query returns the results immediately. Thank you!