How to specify table for deletion in multi-table delete query

R

Renee Moffett

Can someone help me alter the following SQL so that records which are found
in both the table and query are deleted from tblDirSelection? THANK YOU!

DELETE tblDirSelection.Concept, tblDirSelection.Company_ID,
tblDirSelection.Code
FROM qryRoutMaintChangedTerritoryDeletions INNER JOIN tblDirSelection ON
(qryRoutMaintChangedTerritoryDeletions.Code = tblDirSelection.Code) AND
(qryRoutMaintChangedTerritoryDeletions.Company_ID =
tblDirSelection.Company_ID);
 
R

Renee Moffett

Habib was trying to help me in the compuserve forum and asked me to post the
subqueries here. We changed the code for this query to the following.

DELETE DISTINCTROW tblDirSelection.*
FROM tblDirSelection INNER JOIN qryRoutMaintChangedTerritoryDeletions ON
(tblDirSelection.Code = qryRoutMaintChangedTerritoryDeletions.Code) AND
(tblDirSelection.Company_ID =
qryRoutMaintChangedTerritoryDeletions.Company_ID);

When I did that, I got an error that "Access cannot delete records from the
specified table."

We think it is because qryRoutMaintChangedTerritoryDeletions also uses
tblDirSelection. I'm going to create a table and refer to it in the query
instead, just so I can get the immediate need met. I'm told this is not the
ideal solution, so I'm posting SQL for all the supporting queries in hopes
of getting this to work from the query instead. It's deep and complicated.
Sorry.

The intention behind all this is to update a file called tblDirSelection.
It is created from records that match to zip codes in a company's territory.
The territory changes with an update received from the client. I've got it
adding appropriate records. This is to delete records which are no longer
appropriate because of changes in zip territories.

Thanks, Habib and everyone.

qryRoutMaintChangedTerritoryDeletions------------------

SELECT tblDirSelection.Concept, tblDirSelection.Company_ID,
tblDirSelection.Code, tblDirSelection.MaxOfinclusion,
tblDirSelection.AvgOfInclusion, tblDirSelection.ContractComments,
tblDirSelection.NumOfZips, tblDirSelection.Usage,
tblDirSelection.OwnerOccHUs, tblDirSelection.[OwnerOcc>10YrsHUs],
tblDirSelection.LocalORNational, tblDirSelection.LocalAdvType,
tblDirSelection.UpdateDate, tblDirSelection.DateAdded
FROM (tblDirSelection LEFT JOIN qryRoutMaintChangedTerritory ON
(tblDirSelection.Code = qryRoutMaintChangedTerritory.Code) AND
(tblDirSelection.Company_ID = qryRoutMaintChangedTerritory.Company_ID)) LEFT
JOIN tblRoutMaintLimboFranchisees ON tblDirSelection.Company_ID =
tblRoutMaintLimboFranchisees.Company_ID
WHERE (((qryRoutMaintChangedTerritory.Company_ID) Is Null) AND
((tblRoutMaintLimboFranchisees.Company_ID) Is Null) AND
((qryRoutMaintChangedTerritory.Code) Is Null))
ORDER BY qryRoutMaintChangedTerritory.Company_ID;


qryRoutMaintChangedTerritory--------------

SELECT qryRoutMaintChangedTerritoryBasis.Concept,
qryRoutMaintChangedTerritoryBasis.Company_ID,
qryRoutMaintChangedTerritoryBasis.Code,
Max(qryRoutMaintChangedTerritoryBasis.inclusion) AS MaxOfinclusion,
Avg(qryRoutMaintChangedTerritoryBasis.inclusion) AS AvgOfinclusion,
Count(qryRoutMaintChangedTerritoryBasis.Territory_Zip) AS NumOfZips,
qryRoutMaintChangedTerritoryBasis.[Usage%] AS [Usage],
[OwnerOccHUs]*[Usage%] AS AdjOwnerOccHU,
qryRoutMaintChangedTerritoryBasis.OwnerOccHUs,
qryRoutMaintChangedTerritoryBasis.OwnerOccBeforeMar90, [Dir Profiles].CLOSE,
[Dir Profiles].STAT, [Dir Profiles].[E DIRECTORY NAME], [Dir Profiles].[Pub
Init], [Dir Profiles].ISSUE
FROM qryRoutMaintChangedTerritoryBasis LEFT JOIN [Dir Profiles] ON
qryRoutMaintChangedTerritoryBasis.Code = [Dir Profiles].CODE
GROUP BY qryRoutMaintChangedTerritoryBasis.Concept,
qryRoutMaintChangedTerritoryBasis.Company_ID,
qryRoutMaintChangedTerritoryBasis.Code,
qryRoutMaintChangedTerritoryBasis.[Usage%], [OwnerOccHUs]*[Usage%],
qryRoutMaintChangedTerritoryBasis.OwnerOccHUs,
qryRoutMaintChangedTerritoryBasis.OwnerOccBeforeMar90, [Dir Profiles].CLOSE,
[Dir Profiles].STAT, [Dir Profiles].[E DIRECTORY NAME], [Dir Profiles].[Pub
Init], [Dir Profiles].ISSUE
ORDER BY qryRoutMaintChangedTerritoryBasis.Concept,
qryRoutMaintChangedTerritoryBasis.Company_ID, [OwnerOccHUs]*[Usage%] DESC;


qryRoutMaintChangedTerritoryBasis------------------


SELECT tblLocationMaintenance.Concept,
qryRoutMainUniComp_IDWithTerritoryChange.Company_ID, [Zip to Dir].[directory
no] AS Code, [Zip to Dir].inclusion,
tblActive_TerritoryForRegularUpdate.Territory_Zip, [Text Usage].[Usage%],
tblDemographics.OwnerOccHUs, tblDemographics.OwnerOccBeforeMar90
FROM (((((qryRoutMainUniComp_IDWithTerritoryChange LEFT JOIN
tblActive_TerritoryForRegularUpdate ON
qryRoutMainUniComp_IDWithTerritoryChange.Company_ID =
tblActive_TerritoryForRegularUpdate.Company_ID) LEFT JOIN [Zip to Dir] ON
tblActive_TerritoryForRegularUpdate.Territory_Zip = [Zip to Dir].[zip code])
LEFT JOIN tblLocationMaintenance ON
qryRoutMainUniComp_IDWithTerritoryChange.Company_ID =
tblLocationMaintenance.Company_ID) LEFT JOIN [Text Usage] ON [Zip to
Dir].[directory no] = [Text Usage].Code) LEFT JOIN [Dir Profiles] ON [Zip to
Dir].[directory no] = [Dir Profiles].CODE) LEFT JOIN tblDemographics ON [Zip
to Dir].[directory no] = tblDemographics.Code
WHERE ((([Zip to Dir].[directory no]) Is Not Null) AND (([Zip to
Dir].inclusion)>=10) AND (([Dir Profiles].Comments) Is Null));


qryRoutMainUniComp_IDWithTerritoryChange------------------

SELECT DISTINCT Company_ID
FROM qryRoutMainTerritorySuspectedInvalidZips

UNION SELECT DISTINCT Company_ID
FROM qryRoutMainTerritorySuspectedNewZips;


qryRoutMainTerritorySuspectedInvalidZips------------------

SELECT tblActiveTerritory.Company_ID, tblActiveTerritory.Territory_Country,
tblActiveTerritory.Territory_State, tblActiveTerritory.Territory_County,
tblActiveTerritory.Territory_Zip
FROM tblActiveTerritory LEFT JOIN tblActive_TerritoryForRegularUpdate ON
(tblActiveTerritory.Territory_Zip =
tblActive_TerritoryForRegularUpdate.Territory_Zip) AND
(tblActiveTerritory.Company_ID =
tblActive_TerritoryForRegularUpdate.Company_ID)
WHERE (((tblActiveTerritory.Territory_Country)="united states") AND
((tblActive_TerritoryForRegularUpdate.Company_ID) Is Null) AND
((tblActive_TerritoryForRegularUpdate.Territory_Zip) Is Null));

qryRoutMainTerritorySuspectedNewZips------------------

SELECT tblActive_TerritoryForRegularUpdate.Company_ID,
tblActive_TerritoryForRegularUpdate.Territory_Country,
tblActive_TerritoryForRegularUpdate.Territory_State,
tblActive_TerritoryForRegularUpdate.Territory_County,
tblActive_TerritoryForRegularUpdate.Territory_Zip
FROM tblActive_TerritoryForRegularUpdate LEFT JOIN tblActiveTerritory ON
(tblActive_TerritoryForRegularUpdate.Territory_Zip =
tblActiveTerritory.Territory_Zip) AND
(tblActive_TerritoryForRegularUpdate.Company_ID =
tblActiveTerritory.Company_ID)
WHERE (((tblActive_TerritoryForRegularUpdate.Territory_Country)="United
States" Or (tblActive_TerritoryForRegularUpdate.Territory_Country)="Canada")
AND ((tblActive_TerritoryForRegularUpdate.Territory_Zip) Is Not Null) AND
((tblActiveTerritory.Company_ID) Is Null) AND
((tblActiveTerritory.Territory_Zip) Is Null));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top