Solved

Delete Query

Posted on 2008-10-27
1
160 Views
Last Modified: 2010-03-20
I have an Access database table (on a web server).

There are 4 identical records, I want to delete 3 & keep 1.

Is there a way to do this? If I do "DELETE Orders where......." all the where criteria are the same so it will delete ALL, which I don't want.

Of course I recognize I can save the data from 1 & then re-insert, but that's a pain.

Thanks
0
Comment
Question by:Richard Korts
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22817288
you have 2 options:
* create a (temporary) primary key field, so you ARE able to distinguish the records
* copy a single distinct record into a temp table (which has the same structure as the source table), delete all the duplicates, and insert back the distinct record.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Unable to save view in SSMS 21 60
Sub Reports 8 23
How can I name a sub macro so Shift/F10 would work. 3 23
ms/access vba - how to wait for a form to close 2 18
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 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

28 Experts available now in Live!

Get 1:1 Help Now