I have a list of checklist items (tblCheckListItems) which can be selected for use with a case via a combo. Once selected the item cannot be reselected. I had the idea of setting the combo row source with SQL which will only show those items which have not been used. Checklist items used on a case are held in tblCaseCheckList.

Here is the SQL that I thought would work. The sub-query select all the items which have been used on the case (9000 in this example). I thought by setting the main query to show all the checklist items, I could use NOT EXISTS to just display the un-used items.

Here is my SQL....

SELECT tblCheckListItems.id, tblCheckListItems.ListItem
FROM tblCheckListItems
WHERE NOT EXISTS
(SELECT tblCheckListItems.id
FROM tblCaseCheckList INNER JOIN tblCheckListItems ON tblCaseCheckList.ListID = tblCheckListItems.id
WHERE (((tblCaseCheckList.CaseID)=9000)));

..... which retrieves no records ! The subquery on its own gives the 4 items used on the case, the main query on its own shows all 9 checklist items.

Please will someone tell me where I'm going wrong !

Peter

Hi Peter,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

I rarely use the EXISTS keyword, so I can't really tell you why your query doesn't work.

Take a look at the definition of that keyword at MSDN. See if you can modify your query to match their example.

If not, I would recommend trying the IN() clause, which is what I usually use because it makes more sense to me.

Just my 2 cents...

Sorry - Access 2010.

Thanks for this - I'll try with 'IN' and also check out the reference on EXISTS.

I'll let you know what I come up with.

Peter

Hi Peter,



Good luck with your project. Let us know how it goes...

Hi,

Yes using NOT IN sorts the problem - here is the corrected SQL:

SELECT tblCheckListItems.id, tblCheckListItems.ListItem
FROM tblCheckListItems
WHERE ((( NOT ID IN (SELECT tblCheckListItems.id
FROM tblCaseCheckList INNER JOIN tblCheckListItems ON tblCaseCheckList.ListID = tblCheckListItems.id
WHERE (((tblCaseCheckList.CaseID)=9000))))<>False));

Thanks very much for your help.

I'm now off to find out a little bit more about EXISTS..... !

Peter

Hi Peter,

Congratulations! Thanks for the update.

If you find out anything useful about the EXISTS keyword, I would love to hear it.

Continued success with your project.

Cheers