Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Problem deleting duplicate records.

Posted on 2015-02-19
4
46 Views
Last Modified: 2015-03-03
I have a table of parts reservations that includes unique Reservation and (many to one) parts numbers. There cannot be duplicate part numbers for any Reservation. I've isolated the duplicates by creating a query of distinct reservation and parts records by using Max of the IDs for the distinct and selecting the IDs in the table Not Like the Max of IDs.  The delete query says I need to specify a table to delete from, but I don't get it.  The SQL looks like this:
DELETE PickListT.PID
FROM DuplicatePartsRsrvtnsQ INNER JOIN PickListT ON DuplicatePartsRsrvtnsQ.PID = PickListT.PID;

Open in new window

But I get this:
When I try to run the delete query, I get this.What am I missing?
0
Comment
Question by:Jay Williams
  • 2
4 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40618825
Try using a NOT IN ( ) clause rather than a join

DELETE FROM PickListT
WHERE [ID] NOT IN (SELECT PID FROM DuplicatePartsRsrvtnsQ)

NOTE:  Make a backup copy of your database before you try this!!!!!
0
 

Expert Comment

by:Omer-Pitou
ID: 40618890
Use this syntax:
Delete <alias to delete> From table1 alias1 Inner Join table2 alias1 ON alias1.keyField=alias2.keyField.
This syntax worked in MySQL and SQL Server, and should work in your case.
In brief, just after DELETE you should only put the name of the alias you want to delete.
Your correct statement in your case is
DELETE t2
FROM DuplicatePartsRsrvtnsQ t1 INNER JOIN PickListT t2 ON t1.PID = t2.PID;
Note: I just corrected your statement, without questioning what you're trying to achieve.
0
 

Accepted Solution

by:
Jay Williams earned 0 total points
ID: 40625619
Thanks for the input.  I realized there is a preferable strategy, and that is to update the records (for review) and then query and delete the updated records post review.
0
 

Author Closing Comment

by:Jay Williams
ID: 40641476
I realized that deleting the records before reviewing them may not always be the best solution, so I chose the stated approach.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

828 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