Delete query assistance (1 Viewer)

[Isskint]

Hi Guys

I am looking for a solution to delete records in one table where a field = a form selected value and the FK is also in another table where a field there = a form selected value.

Setting:
I have a join table (it creates combinations of Sites and Sectors covered by each Site). The PK is [slID]. Now each combination of Site/Sector will have multiple products and multiple points of contact. Products and Contacts are not exclusive to a site/sector so I have Product List and Contacts List tables. To join these to the Site/Sector, I have Product and Contact join tables that use [slID] as a FK.
On a contacts record form, i display listboxes showing the Sites, Sectors and Products that contact covers. From here i need to provide the ability for users to delete a selected product from the Products table for that contact only. - Sites/Sectors can NOT be deleted due to entries in other tables that can not be deleted (call log and orderbook).

I am sure the answer is the 2 SQL below (one using IN can the other EXISTS) but i just can't quite get it. I get syntax errors, invalid SELECT errors and Invalid Join errors
I have included an image of the DB relationships in case that can be simplified and thus provide the answer easier than a convoluted query.

DELETE Products.*, Site_Sector_Contact_Join.slid, Products.prodCode
FROM Products LEFT JOIN Site_Sector_Contact_Join ON Products.slID IN SELECT (Site_Sector_Contact_Join.slID FROM Site_Sector_Contact_Join WHERE ((Site_Sector_Contact_Join.conID) =([Forms]![frm_Edit_Contact]![conID])))) 
WHERE (((Site_Sector_Contact_Join.slid) Is Not Null) AND ((Products.prodCode)=[Forms]![frm_Edit_Contact]![lbProduct]));

DELETE Products.*, Site_Sector_Contact_Join.slid, Products.prodCode
FROM Products LEFT JOIN Site_Sector_Contact_Join ON Products.slID EXISTS SELECT (Site_Sector_Contact_Join.slID FROM Site_Sector_Contact_Join WHERE ((Site_Sector_Contact_Join.conID) =([Forms]![frm_Edit_Contact]![conID])))) 
WHERE (((Site_Sector_Contact_Join.slid) Is Not Null) AND ((Products.prodCode)=[Forms]![frm_Edit_Contact]![lbProduct]));

For identification:
Site_Sec_join = join table for Sites/Sectors
Site_Sector_Contact_Join = Join table between Site_Sec_join and Contatcs
Products = join table between Products List and Site_Sec_join
Forms.frm_Edit_Contact.conID = FK in Site_Sector_Contact_Join (PK of Contacts table) Long Integer
Forms.frm_Edit_Contact.lbProduct = FK in Products (PK of Products List) Long Integer

If there is any more info required, please ask.

 

[Ranman256]

Normally you can't delete records in a join.
You must use the IN operator, like in a Find Duplicate query.
Use the Wizard and make a Find Duplicate Query, then look at the design.
The criteria uses this IN in a sub query as criteria.

 

[Isskint]

Thanks Ranman, that is how i came up with the 2 SQL i posted. Will need to replicate this a few times, so wanted to avoid subqueries.