Delete Query Cannot delete from specified tables

[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

 

[Duane Hookom]

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.

 

[ken957]

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));

 

[ken957]

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

 

[Duane Hookom]

Did you "... then define its primary key ..." after creating the table?

--
Duane Hookom
Microsoft Access MVP

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

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));