Help: Need to delete duplicates

I have an access table that has over 470,000 rows.  I need to delete
the duplicates.  None of the "append" query ideas will work as it
involves copying and pasing the structure, and Access won't allow me
to copy 470,000 rows.  Does anyone have a suggestion on how to
accomplish this?
0
smduello
1/18/2008 5:07:54 PM
access 16762 articles. 3 followers. Follow

3 Replies
429 Views

Similar Articles

[PageSpeed] 15

What error do you get when you run the append query? It could be that the 
file size is nearing the 2 GB limit. First make a backup copy of the database 
then run a compact and repair. Then try the append query ideas.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"smduello@gmail.com" wrote:

> I have an access table that has over 470,000 rows.  I need to delete
> the duplicates.  None of the "append" query ideas will work as it
> involves copying and pasing the structure, and Access won't allow me
> to copy 470,000 rows.  Does anyone have a suggestion on how to
> accomplish this?
> 
0
Utf
1/18/2008 5:32:02 PM
On Fri, 18 Jan 2008 09:07:54 -0800 (PST), smduello@gmail.com wrote:

>I have an access table that has over 470,000 rows.  I need to delete
>the duplicates.  None of the "append" query ideas will work as it
>involves copying and pasing the structure, and Access won't allow me
>to copy 470,000 rows.  Does anyone have a suggestion on how to
>accomplish this?

Nobody would suggest copying and pasting ten rows, much less 470000.

Copy and paste the *STRUCTURE* of the table - not the table. Select the table
in the tables window; type Ctrl-C to copy and Ctrl-V to paste. You'll get a
popup window offering three choices - Structure Only, Structure and Data, and
Add records to existing table. Check Structure Only.

You'll then be asked for a new table name. Open this new table, and set a
unique Index on the combination of fields which identify a duplicate.

Compact the database at this point to be sure it's starting clean.

Then create a new Query based on the original table. Change it to an Append
query and select the new table. Run the query using the ! icon; you'll get a
warning message "25236 records were not appended due to key violations" -
that's the duplicates you're trying to get rid of.


             John W. Vinson [MVP]
0
John
1/18/2008 6:58:46 PM
Provided the table has a column (or columns) with unique values, e.g. an 
autonumber, it can be done with a DELETE query.  Say the duplicates are 
identified by LastName and FirstName columns (unrealistic of course as names 
can legitimately be duplicated) and the unique values are in a column YourID:

DELETE *
FROM YourTable As T1
WHERE EXISTS
   (SELECT *
    FROM YourTable As T2
    WHERE T2.LastName = T1.LastName
    AND T2.FirstName = T1.FirstName)
AND YourID >
    (SELECT MIN(YourID)
     FROM YourTable As T3
     WHERE T3.LastName = T1.LastName
     AND T3.FirstName = T1.FirstName);

Ken Sheridan
Stafford, England 

"smduello@gmail.com" wrote:

> I have an access table that has over 470,000 rows.  I need to delete
> the duplicates.  None of the "append" query ideas will work as it
> involves copying and pasing the structure, and Access won't allow me
> to copy 470,000 rows.  Does anyone have a suggestion on how to
> accomplish this?
>

0
Utf
1/19/2008 6:51:01 PM
Reply:

Similar Artilces:

Help with a formula..
I am trying to create a formula that will take information from a cell on one sheet and combine it with text on another sheet. I know how to get the two together. My problem is that I want the part that is brought in to be bolded type. Here is what I have in the formula. ="we are pleased to submit our quotation for "&(cell reference)&" according to the following specifications." What I want to do is have the cell reference part be bold type. Is there a way to do that? It doesnt work if I bold the cell.. already tried it.. Any suggestions? Thanks! KK You'...

Help needed urgently
Hi, Im trying to check that one row of data satifies a criteria, then if it does it counts an entry in another column adjescent to it. Example: - Column A - Column B Blay - Y Blay - N Blay - Y Chop - N Chop - N High - Y What i would be asking (of a much bigger table) is If Column A = Blaydon and then column B (same row) = Y, then count. In this example I would want 2 returned as the answer. Is this possible? Thanks -- Andy Try one of these... Use cells to hold the criteria: D2 = Blaydon E2 = Y If you're using Excel 2007 or later: =COUNTIFS(A2:A7,...

Yet another duplicate record dilemma
I have a table with records where one field are duplicates. I'm able to query to find duplicates and delete them, however what I need to do is find the duplicates, produce a total from another field, delete the duplicates and update the record field with the new total. Use the Find duplicates wizard, the build an Update query and either add to the field: Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = MyField + Query1.MyField or just update it: Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = Query1.MyField Then delete the duplicate data. -- Ar...

help with dynamic tables
This is a bit complicated to explain but I'll try my best. In columns A, B, C I have different drop down lists. Column A has Store1, Store2, Store3, etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB, ProductC. As of right now, these lists are not dependent on each other, I can choose anything from any list regardless of the previous category. Also, the length of these lists is undefined, meaning I will constantly be adding to them in sequential rows below. And then columns D and beyond have data such as Sales, Profits, # of items, etc. What I...

Deleted items folder in o2k3
The big boss refuses to empty or archive his deleted items, now holding some 25,000 messages and approaching 3 gigs in size for the deleted items folder alone. Does Outlook treat messages in the deleted items folder any differently than messages in other folders? Would it be better for him to never delete messages since he doesn't really want to delete them? It would be better to not delete them since Deleted Items... is for things you want to get rid of. "Wowbagger" <none> wrote in message news:uKyAPby2GHA.3656@TK2MSFTNGP04.phx.gbl... > The big boss refuses ...

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

"x" button enables deletion.... can we put a focus trigger there?
Hi, on three forms, I put focus triggers on the "delete" button because I need to validate some additional data before allowing the user to delete. However, if the user clicks on the corner "x" button to close the form and gets the dialogue box saying they have changes, do they want to "Save", "Delete" or "Cancel", they can delete the record anyways. Is there any way to put a focus trigger on the button of the dialogue box, or on the "x" button? Thanks in advance. I assume you are working with Dexterity. To work with an add...

Need code snippet to read offline PST file
Hi friends, I have a PST file in my local hard disk and have requirement to read PST file and parse through all folders and then each message item in all folders and then segregate them to different folders based on subject line. Please kindly send the code for the above requirement. Thanks & Regards Ramesh -- ramserp You're going to have to write your own code. Do you know anything about Outlook programming at all? You can start out by looking at information and code samples at www.outlookcode.com. -- Ken Slovak [MVP - Outlook] http://www.slo...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

Duplicate Containers in AD after Exchange 2003 Migration
The scenario is: We migrated from an NT domain to a 2000 domain. Then about a year later we migrated from exchange 5.5 to exchange 2003. Once the migration was complete, we discovered that there were duplicate containers and OUs in "Active Directory Users and Computers" which were empty. For example, when we did the AD migration we created an OU hierarchy at the root of the domain. After the exchange migration we found a duplicate hierarchy structure under the Users container but it didnt actually contain any objects/information that was in the original OU structure. We don...

Find and mark duplicates
If I have two columns with data, how do I compare these two columns to find all duplicates and put an X on a third colum next to all duplicates? Thanks. =if(a1=b1,"X","") "maxtrixx" <maxtrixx@discussions.microsoft.com> wrote in message news:139BC711-FAAC-4CD2-A533-CE49BA77291F@microsoft.com... > If I have two columns with data, how do I compare these two columns to > find > all duplicates and put an X on a third colum next to all duplicates? > Thanks. The problem is, I don't know which two cells to compare, I need to know if any o...

Duplicate personal folders problem
Hello all, I have a problem with Outlook 2003 and I hope someone can help me resolve it I imported a PST file from Outlook XP that was on a different machine, and now I have two versions of Personal Folders in my All Mail Folders list. They both have Deleted Items, Drafts, Inbox, Junk E-mail, Outbox, Sent Items and Search Folders in. One of them has an icon of a piece of paper with a clock to the top left and a house to the top right. The other one has an icon like a stack of files (like from a filing cabinet) They are clearly referencing the same thing, as the Deleted Items both have al...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

make outlook automatically delete html mail with linked images
The junk mail filter should be able to automatically delete html mail with linked images ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=9e1b783b-c9c1-44f2-a1dd-93da59216c56&dg=microsoft.public.outloo...

Retreive a deleted file
How can I retreive a delted file Hi Janet If you have setup Excel to automatically create backup copies; then check that folder for the last backed-up version....If it is from your PC, check out the Recylcle Bin (on your desktop) and restore the file. If the file was on a network drive you may need to contact your network administrator and check whether they can recover. -- Jacob (MVP - Excel) "Janet" wrote: > How can I retreive a delted file Go into your Recycle Bin. If deleted from there; http://www.ozgrid.com/Services/recover-deleted-files.htm ...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...

Macro Help #18
Well I think this is a macro. I have searched my excel book and i can not seem to figure out what to do. I want to write something that if this statement is true, then the font color changes. But here is the potential problem. THe location of the text will change. So i need it to search for this text and then change the color. Does that make sense? Is this possible? If this can be done it will really help me. Thanks in advance. This is exactly what i am trying to do. You type in a persons name into cell a1. you also type a name into cell a2. These names will transfer over ...

Duplicate Public folders
I am struggling to get all my public folders replicated on both of my servers. The Status seems to be either "local Modified" or "both modified" I then discovered that MOST of my public folders viewed when I clicked on "View System Folders" did not have a listed replica on the other server. So I added a replica for each System Folder. Now I seem to have duplicates of some of these folder listed in the Public folders list of one of my servers. Can someone tell me where I can find a lits of folders that _should_ be replicated and which should not? Many tha...

Newbie needs help with new Exchange 2003 installation
I am TOTALLY new to Exchange. We just installed Exchange Server 2003 on a Windows 2003 domain. I have one (test) account set up and it appears to work OK but I keep getting an error message that says "Task "Microsoft Exchange Server" reported error (0x8004010F): 'The operation failed. An object could not be found.' I know, I know...How could I possibly not understand THIS message? I appear to be getting mail but I' can't be sure. How can I check to see what's causing this (and more important...what object it's looking for?) Thanks for any help! -Fran- ...

How to delete incorrect entries from Dictionary
On occasion, Outlook 2000 recommends a spelling that's obviously incorrect. I suppose that I clicked on a word too hastily. I'd like to remove these incorrect entries but how? tools, options, spelling, edit dictionary (from any app that supports the office spell check) - its also available from the spell check screen when you press F7 or you can search for custom.dic and edit it with notepad. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visu...

Deleting a Batch from company DB table SY00500
This is a multi-part message in MIME format. ------=_NextPart_000_002F_01C96F54.F7568BB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there a way to see all the TRX in a Batch saved in SY00500. Also, what if we delete a batch in SY00500 will there be any other place = to look for; besides this? I had a batch sitting here for a long time = and there were few (3) trxs ( Sales Credits) in it as well. I wonder if I could retrieve those one by one and then delete them as = well as these belong to a fiscal period which is closed plus we...

Help
Hi... I tried to work on Word today for first time on newish Mac and realized none of my Office applications work. When I double click on Word I can see something happening as I get the circle of colours twirling, but half an hour later it still hadn't opened. I just have to do this as quickly as possible as I have a deadline for what I have to do Monday at 12. I've tried reinstalling the software, but that didn't do anything for me. Mind you I know absolutely nothing about installing anything on Mac computers so I could be doing it all wrong. Do any of you have any idea what c...

I Need an answer for this Formula
I am using excell 2007 & this formula works {=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} When i upload this workbook to a 2003 version this formula does not work I get {=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} or somthing close to this Then in the cell with this formula has a NAME error WHY & HOW could i fix The IFERROR function can only be used in Excel 2007. Try this array formula** : =LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))...

Macro help with saving a spreadsheet with date and time in it
Can someone help me with some code that would save a file name as "schedule-mm-dd-yyyy-hh:mm"? Thanks, Alan Alan, how about something like this Sub Save_As() ActiveWorkbook.SaveAs Filename:="Schedule " & Format(Now, "mm-dd-yyyy-hh-mm") & ".xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Help!
We went live with 4 decimals on our functional currency and we now want to reduce them to 2 decimals. All of our transactions so far are in 4 decimals. Can anyone guide me into doing this? thank you Use the Change Decimal Places window to change the number of decimal places used for keeping quantity or currency amounts for items. You might need to complete this procedure if an item was set up improperly, or if you’re changing the units of measurement for an item. NOTE: If an item is on an unposted transaction, you can’t change its decimal place settings. Ensure you have a complete ba...