Not Exists in subquery retieves all records 1

[brendanmc]

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

 

[PHV]

You may try this:
WHERE Not tblClientDemographics.IDNumber In (SELECT IDNumber FROM bmc_admin_discharges_fernhill_sub)
Or this:
WHERE tblClientDemographics.IDNumber Not In (SELECT IDNumber FROM bmc_admin_discharges_fernhill_sub)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[brendanmc]

Thanks PH.

Unfortunately I get the message Query to complex. maybe its related to MS access being version 97? I tried both suggestions. (If I remove the NOT so it only has IN, it works, but I get the reverse of what I'm after)

I tried replcaing NOT IN with NOT EXISTS but got syntax errors. The only way I can get NOT EXISTS to give a result (an incorrect result) is to use this:

WHERE((Not (tblClientDemographics.IDNumber)=Exists (SELECT IDNumber FROM bmc_admin_discharges_fernhill_sub)))

I'm enjoying trying to nut this out, but this has got me stumped!

 

[PHV]

And this:
WHERE Not Exists (SELECT * FROM bmc_admin_discharges_fernhill_sub WHERE IDNumber = tblClientDemographics.IDNumber)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[brendanmc]

An interesting result this time. I have no rows returned at all. I removed all other criteria to no effect.

Comparing the results manually, I should have about 25 rows.

Would the conditions in the subquery be causing issues? I tried changing the subquery so it only returns ID number to no effect.

I typed the SQL directly into SQL section exactly as you mentioned, but access changed it to:

WHERE (((Exists (SELECT * FROM bmc_admin_discharges_fernhill_sub WHERE IDNumber = tblClientDemographics.IDNumber))=False))

 

[brendanmc]

PHV, looks like we have a solution. The criteria you gave me helped with my not exists syntax, so thanks for that (I put a star on a previous post that you entered).

What I was doing was comparing the ID number from a table that lists every single ID in the system, hence no results.

I ended up creating another subquery that listed all the people in the service unit. The created a final query that used the not exist clause, using both subqueries rather than the table. See below for SQL if anybody is interested - you can compare against original post for incorrect query

Thanks for all your help.
Brendan

SELECT bmc_admin_discharges_fernhill_sub2.IDNumber, bmc_admin_discharges_fernhill_sub2.IDNumber
FROM bmc_admin_discharges_fernhill_sub2
WHERE (((Exists (SELECT * FROM bmc_admin_discharges_fernhill_sub WHERE IDNumber = bmc_admin_discharges_fernhill_sub2.IDNumber))=False));

 

[PHV]

To avoid any ambiguity:
WHERE (((Exists (SELECT * FROM bmc_admin_discharges_fernhill_sub WHERE bmc_admin_discharges_fernhill_sub.IDNumber = tblClientDemographics.IDNumber))=False))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244