Hi, I'm trying to get a list of patients in one service that are not being seen by another service concurrently. I'm basing the consition on the patient ID.
I have a subquery which lists all patients currently being seen which are not in the service I am trying to get the results for('2327' in the code below.)
I have found that the - 'NOT IN' [select ID .. subquery] - clause does not work as a query too complex message appears. If I use -IN [select ID .. subquery], I get the reverse of what I want, as expected (patients concurrently being seen).
When using NOT EXISTS [select ID .. subquery], I'm finding that all patients are being retrieved for the service n question, even if they are being seen.
I'm on access 97, and added the not exists clause via design view.
Thanks Brendan
(I've removed alot of the SQL for ease of view, if you want the whole lot let me know)
SELECT Status,sector, EstablishmentID, IDnumber
FROM (tblClientDemographics LEFT JOIN tblCollectionOccasion ON tblClientDemographics.IDNumber = tblCollectionOccasion.IDNumber) INNER JOIN (bmc_service_departments)
WHERE ((Not (tblClientDemographics.IDNumber)=Exists (SELECT IDNumber FROM bmc_admin_discharges_fernhill_sub)))
GROUP BY IDNumber
I have a subquery which lists all patients currently being seen which are not in the service I am trying to get the results for('2327' in the code below.)
I have found that the - 'NOT IN' [select ID .. subquery] - clause does not work as a query too complex message appears. If I use -IN [select ID .. subquery], I get the reverse of what I want, as expected (patients concurrently being seen).
When using NOT EXISTS [select ID .. subquery], I'm finding that all patients are being retrieved for the service n question, even if they are being seen.
I'm on access 97, and added the not exists clause via design view.
Thanks Brendan
(I've removed alot of the SQL for ease of view, if you want the whole lot let me know)
SELECT Status,sector, EstablishmentID, IDnumber
FROM (tblClientDemographics LEFT JOIN tblCollectionOccasion ON tblClientDemographics.IDNumber = tblCollectionOccasion.IDNumber) INNER JOIN (bmc_service_departments)
WHERE ((Not (tblClientDemographics.IDNumber)=Exists (SELECT IDNumber FROM bmc_admin_discharges_fernhill_sub)))
GROUP BY IDNumber