MS Access Specify Table Error

Posted on 2009-12-23
Last Modified: 2013-11-27
I'm getting a "Specify the table containing the records you want to delete." Error.

This is the code I've generated from the Access GUI following the delete query tutorial:
DELETE Customers.*, Customers.[Last Name], Customers.[First Name], Customers.ID, Customers.Company
FROM [Customers - Distinct Query] AS [Distinct] INNER JOIN Customers ON Distinct.[Last Name] = Customers.[Last Name]
WHERE (((Customers.[Last Name])=[Distinct].[Last Name]) AND ((Customers.[First Name])=[Distinct].[First Name]) AND ((Customers.ID)<>[Distinct].[MinOfID]) AND ((Customers.Company)=[Distinct].[Company]));

I want to delete the fields in table Customers that are completely duplicated.  I'm using the ID field, which is the autonumber primary key, to choose which one is to be deleted, using the MinOfID key.  Could you help?
Question by:jbgames
    LVL 10

    Accepted Solution

    I don't believe you can use a join in the FROM clause in a DELETE statement.
    The following should do what you're after (but usual warnings apply - try it out on a spare copy of your data first!)

    DELETE * FROM Customers C1
    (SELECT Min(C2.ID) FROM Customers C2
    GROUP BY C2.[Last Name], C2.[First Name], C2.Company);


    Author Comment

    What does the C1 / C2 do?  Do I need to change things in my setup first?

    Author Comment

    Thank you so much!

    Author Comment

    It worked!  No more info needed!

    Author Closing Comment

    LVL 10

    Expert Comment

    For the record, C1 and C2 are aliases, or short 'nicknames' given to the Customers table. Aliases help to make queries more readable and avoid ambiguity. In this particular case it wasn't essential to use them, but there are some queries containing subselects where you would have to use them.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    The canonical version of this article is on my web site here: A companion presentation is available here:
    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now