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_PeopleJoinD istro) 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_PeopleJoinD istro) and identifies only those which are also contained in an old Distribution list. This second query is called qry_Distro_Anal ytics_ByDistroL ist.

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_PeopleJoin Distro", 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_Dash Board_Super]![txtDistroList]"

The Select Query Looks Like this:

SELECT
tbl_Distro_Join _People.Distrib ution_List, tbl_Distro_Join _People.People
FROM
(tbl_People INNER JOIN qry_PeopleJoinD istro ON tbl_People.[Personal Contact Serial] = qry_PeopleJoinD istro.[Personal Contact Serial]) INNER JOIN tbl_Distro_Join _People ON tbl_People.[Personal Contact Serial] = tbl_Distro_Join _People.People
WHERE (((tbl_Distro_J oin_People.Dist ribution_List)=[Forms]![frm_Distro_Dash Board_Super]![txtDistroList]) AND ((tbl_Distro_Jo in_People.Peopl e)=[qry_PeopleJoinD istro]![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_PeopleJoinD istro) 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_PeopleJoinD istro) and identifies only those which are also contained in an old Distribution list. This second query is called qry_Distro_Anal ytics_ByDistroL ist.

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_PeopleJoin Distro", 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_Dash Board_Super]![txtDistroList]"

The Select Query Looks Like this:

SELECT
tbl_Distro_Join _People.Distrib ution_List, tbl_Distro_Join _People.People
FROM
(tbl_People INNER JOIN qry_PeopleJoinD istro ON tbl_People.[Personal Contact Serial] = qry_PeopleJoinD istro.[Personal Contact Serial]) INNER JOIN tbl_Distro_Join _People ON tbl_People.[Personal Contact Serial] = tbl_Distro_Join _People.People
WHERE (((tbl_Distro_J oin_People.Dist ribution_List)=[Forms]![frm_Distro_Dash Board_Super]![txtDistroList]) AND ((tbl_Distro_Jo in_People.Peopl e)=[qry_PeopleJoinD istro]![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 tblDistribution List_New that have identical Records in tblDistribution List_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]);