Delete Query with an Inner Join

[Nettle]

Purpose:
This is a Distribution List database.

Function:
Users create many different email distribution lists, tailoring each to fit their specific needs

Wanted:
Users can combine multiple distribution lists to create new ones. Sometimes, however, they discover they've made a mistake. Users would like to delete from a new distribution list only the records that belong to an old one.

Technique (Short)
I've created a query (qry_PeopleJoinDistro) that identifies all records in the new distribution list.

I have created a second query which looks at all the records in the new Distribution list (qry_PeopleJoinDistro) and identifies only those which are also contained in an old Distribution list. This second query is called qry_Distro_Analytics_ByDistroList.

A schematic of this would look like two overlapping circles. Each circle represents a Distribution list. The intersecting region would represent those records contained in both.

My goal would be to delete overlapping records from the new distribution list ONLY. After removing these records, the schematic represented by two circles above would no longer have an intersecting region.


Technique (Long)- Let the Madness Begin!
A query selects the records in a new distribution list with the query called, "qry_PeopleJoinDistro", which is based on an open user form.

These records are narrowed, according to old distribution lists, with a second user controlled field called "[Forms]![frm_Distro_DashBoard_Super]![txtDistroList]"

The Select Query Looks Like this:

SELECT
tbl_Distro_Join_People.Distribution_List, tbl_Distro_Join_People.People
FROM
(tbl_People INNER JOIN qry_PeopleJoinDistro ON tbl_People.[Personal Contact Serial] = qry_PeopleJoinDistro.[Personal Contact Serial]) INNER JOIN tbl_Distro_Join_People ON tbl_People.[Personal Contact Serial] = tbl_Distro_Join_People.People
WHERE (((tbl_Distro_Join_People.Distribution_List)=[Forms]![frm_Distro_DashBoard_Super]![txtDistroList]) AND ((tbl_Distro_Join_People.People)=[qry_PeopleJoinDistro]![Personal Contact Serial]));

Summary of My Problem:
Turn the SELECT query above into a DELETE query. Records should be removed from tbl_Distro_Join_People.

[ADezii]

Purpose:
This is a Distribution List database.

Function:
Users create many different email distribution lists, tailoring each to fit their specific needs

Wanted:
Users can combine multiple distribution lists to create new ones. Sometimes, however, they discover they've made a mistake. Users would like to delete from a new distribution list only the records that belong to an old one.

Technique (Short)
I've created a query (qry_PeopleJoinDistro) that identifies all records in the new distribution list.

I have created a second query which looks at all the records in the new Distribution list (qry_PeopleJoinDistro) and identifies only those which are also contained in an old Distribution list. This second query is called qry_Distro_Analytics_ByDistroList.

A schematic of this would look like two overlapping circles. Each circle represents a Distribution list. The intersecting region would represent those records contained in both.

My goal would be to delete overlapping records from the new distribution list ONLY. After removing these records, the schematic represented by two circles above would no longer have an intersecting region.


Technique (Long)- Let the Madness Begin!
A query selects the records in a new distribution list with the query called, "qry_PeopleJoinDistro", which is based on an open user form.

These records are narrowed, according to old distribution lists, with a second user controlled field called "[Forms]![frm_Distro_DashBoard_Super]![txtDistroList]"

The Select Query Looks Like this:

SELECT
tbl_Distro_Join_People.Distribution_List, tbl_Distro_Join_People.People
FROM
(tbl_People INNER JOIN qry_PeopleJoinDistro ON tbl_People.[Personal Contact Serial] = qry_PeopleJoinDistro.[Personal Contact Serial]) INNER JOIN tbl_Distro_Join_People ON tbl_People.[Personal Contact Serial] = tbl_Distro_Join_People.People
WHERE (((tbl_Distro_Join_People.Distribution_List)=[Forms]![frm_Distro_DashBoard_Super]![txtDistroList]) AND ((tbl_Distro_Join_People.People)=[qry_PeopleJoinDistro]![Personal Contact Serial]));

Summary of My Problem:
Turn the SELECT query above into a DELETE query. Records should be removed from tbl_Distro_Join_People.

This looks like the perfect situation for a Sub-Query. The following Query will Delete all Records in tblDistributionList_New that have identical Records in tblDistributionList_Old based on the [DistributionID] Field. You should be able to adapt the SQL for your specific needs:

DELETE  FROM tblDistributionList_New
WHERE EXISTS 
(SELECT [DistributionID]                                       
FROM tblDistributionList_Old                                     
WHERE tblDistributionList_Old.[DistributionID] = tblDistributionList_New.[DistributionID]);