Delete query error when running question

[John]

Hi

I have the below query and I am trying to delete records from the contacts
table based on the link with the other table and condition.

DELETE Contacts.ID
FROM Contacts INNER JOIN [a_Unique Contacts] ON Contacts.[Company ID] =
[a_Unique Contacts].[Company ID]
WHERE (((Contacts.ID)<>[a_Unique Contacts].[MinOfID]));

When I try to run the query I get the message 'Specify the table containing
the records you want to delete'. What is the problem and how can I fix it?

Thanks

Regards

 

[Wolfgang Kais]

Hello John.

I have the below query and I am trying to delete records from the
contacts table based on the link with the other table and condition.

DELETE Contacts.ID FROM Contacts INNER JOIN [a_Unique Contacts]
ON Contacts.[Company ID] = [a_Unique Contacts].[Company ID]
WHERE (((Contacts.ID)<>[a_Unique Contacts].[MinOfID]));

When I try to run the query I get the message 'Specify the table
containing the records you want to delete'.
What is the problem and how can I fix it?

A delete query deletes rows from a specified table. Access has to know
the table you want to delete from (Contacts or a_Unique Contacts)?
For a delete query with multiple tables, in query design view, drag
the asterisk (*) of from that table to the QBE grid.
The query's SQL then looks like this:

DELETE Contacts.*, Contacts.ID
FROM Contacts INNER JOIN [a_Unique Contacts]
ON Contacts.[Company ID] = [a_Unique Contacts].[Company ID]
WHERE (((Contacts.ID)<>[a_Unique Contacts].[MinOfID]));

I suppose that a_Unique Contacts is a query that returns the minimum
ID per company, right? If so, you will receive another error message,
stating that the deletion is not possible. That is because the
resulting recordset isn't updatable. Try this:

Delete Contacts.* Where Exists (Select * From Contacts As Tmp
Where Tmp.[Company ID] = Contacts.[Company ID] And
Tmp.ID < Contacts.ID);

If you want every company to only have one contact, store the
contact information in the Companies table.

 

[Klein-4]

Hi

I have the below query and I am trying to delete records from the contacts
table based on the link with the other table and condition.

DELETE Contacts.ID
FROM Contacts INNER JOIN [a_Unique Contacts] ON Contacts.[Company ID] =
[a_Unique Contacts].[Company ID]
WHERE (((Contacts.ID)<>[a_Unique Contacts].[MinOfID]));

When I try to run the query I get the message 'Specify the table containing
the records you want to delete'. What is the problem and how can I fix it?

Thanks

Regards

I believe changing

DELETE Contacts.ID

to DELETE Contacts.*

will do the trick.

Let me know if not.

Ross La Haye