Delete query error when running question


J

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
 
Ad

Advertisements

W

Wolfgang Kais

Hello John.

John said:
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.
 
Ad

Advertisements

K

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
 

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