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
WHERE NOT EXISTS
FROM tblCaseCheckList INNER JOIN tblCheckListItems ON tblCaseCheckList.ListID = tblCheckListItems.id
..... 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 !