By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,596 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Delete Query with an Inner Join

P: 4
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.
Apr 19 '08 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,599
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:
Expand|Select|Wrap|Line Numbers
  1. DELETE  FROM tblDistributionList_New
  2. WHERE EXISTS 
  3. (SELECT [DistributionID]                                       
  4. FROM tblDistributionList_Old                                     
  5. WHERE tblDistributionList_Old.[DistributionID] = tblDistributionList_New.[DistributionID]); 
Apr 20 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.