If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Delete Query Cannot delete from specified tables
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 -- ken |
#2
|
|||
|
|||
Delete Query Cannot delete from specified tables
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. -- Duane Hookom Microsoft Access MVP "ken957" wrote: 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 -- ken |
#3
|
|||
|
|||
SQL of above
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)); -- ken |
#4
|
|||
|
|||
same error
I made a new table pasted in the records of unique company names and still
get the cannot delete from specified tables -- ken "ken957" wrote: 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)); -- ken |
#5
|
|||
|
|||
same error
Did you "... then define its primary key ..." after creating the table?
-- Duane Hookom Microsoft Access MVP "ken957" wrote: I made a new table pasted in the records of unique company names and still get the cannot delete from specified tables -- ken "ken957" wrote: 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)); -- ken |
Thread Tools | |
Display Modes | |
|
|