Solved

Problem deleting duplicate records.

Posted on 2015-02-19
4
43 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

705 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

21 Experts available now in Live!

Get 1:1 Help Now