Delete Query Cannot delete from specified tables

  • Thread starter Thread starter ken957
  • Start date Start date
K

ken957

I have a very long table using a limited number of company names, I have
another long table with many of the same or similar company names. I wanted
to make a table without duplicates of the the company names used in the first
table, then work on establishing relationships with the names from the second
table.

I made a table with only three fields, autonumber, CompanyNameListA,
CompanyNameListB. I pasted all 20,000 plus companynames from listA, made a
totals query grouping on company name and using MinOf the autonumber field.

My delete query correctly displays the records I want to delete but only
gives the "cannot delete from specified tables" error

The only relationship between any tables/querys is between company names
from ListA and the Totals Query
 
Any query that contains GROUP BY in it or one of its sources is not
updateable. I generally create a table based on the GROUP BY query and then
define its primary key. Use this new query in place of your totals query.
 
This is the SQL of my post. I changed MinOf to FirstOf but same result


DELETE CompanyNamesCompare.*, CompanyNamesCompare.IBAMAT_Company,
CompanyNamesCompare.ID, CompanyNamesCompare.ID
FROM CompanyNamesCompare INNER JOIN NodupsIBcompany AS DDD ON
CompanyNamesCompare.IBAMAT_Company = DDD.IBAMAT_Company
WHERE (((CompanyNamesCompare.IBAMAT_Company)=[DDD].[IBAMAT_Company]) AND
((CompanyNamesCompare.ID)<>[DDD].[FirstOfID] And
(CompanyNamesCompare.ID)<200));
 
I made a new table pasted in the records of unique company names and still
get the cannot delete from specified tables
 
Did you "... then define its primary key ..." after creating the table?

--
Duane Hookom
Microsoft Access MVP


ken957 said:
I made a new table pasted in the records of unique company names and still
get the cannot delete from specified tables
--
ken


ken957 said:
This is the SQL of my post. I changed MinOf to FirstOf but same result


DELETE CompanyNamesCompare.*, CompanyNamesCompare.IBAMAT_Company,
CompanyNamesCompare.ID, CompanyNamesCompare.ID
FROM CompanyNamesCompare INNER JOIN NodupsIBcompany AS DDD ON
CompanyNamesCompare.IBAMAT_Company = DDD.IBAMAT_Company
WHERE (((CompanyNamesCompare.IBAMAT_Company)=[DDD].[IBAMAT_Company]) AND
((CompanyNamesCompare.ID)<>[DDD].[FirstOfID] And
(CompanyNamesCompare.ID)<200));
Click to expand...
Click to expand...
 
Back
Top