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

Hello John.

"John" wrote:
> 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.

-- 
Regards,
Wolfgang 

On Oct 23, 10:13 pm, "John" <J...@nospam.infovis.co.uk> wrote:
> 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