A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Deleting duplicate records



 
 
Thread Tools Display Modes
  #1  
Old June 29th, 2004, 07:20 PM
Alice Dawson
external usenet poster
 
Posts: n/a
Default Deleting duplicate records

I have a table: [Related Submissions]
Fields: Autonumber (Primary key), [SUbmission Number 1], [Submission Number 2], Relationship
Using Access 2002

I want to remove duplicate records where for two records, [Submission number 1], Submission Number 2] and Relationship are the same. It is easy to find the duplicates, but is there any way to remove one of the duplicate records?

Thanks

Alice
  #2  
Old June 29th, 2004, 10:50 PM
BerHav
external usenet poster
 
Posts: n/a
Default Deleting duplicate records

Hi Alice,

Create a query, add the fields without any criteria, change to SQL view and amend the script to
SELECT Distinct ....


--
HTH
Bernd


"Alice Dawson" wrote:

I have a table: [Related Submissions]
Fields: Autonumber (Primary key), [SUbmission Number 1], [Submission Number 2], Relationship
Using Access 2002

I want to remove duplicate records where for two records, [Submission number 1], Submission Number 2] and Relationship are the same. It is easy to find the duplicates, but is there any way to remove one of the duplicate records?

Thanks

Alice

  #3  
Old June 30th, 2004, 07:53 AM
Raghu Prakash
external usenet poster
 
Posts: n/a
Default Deleting duplicate records

Hi Alice,

Finding and deleting duplicate records in Access:
To delete duplicate records and keep the originals automatically
Deleting duplicate records from a table requires two steps. First, you
create a copy of the structure of the table that contains duplicates and
make primary keys of all the fields that contain duplicates. Second, you
create and run an append query from the original table to the new table.
Because fields that are primary keys can't contain duplicate records, this
procedure produces a table without duplicate records.

To copy the table and make primary keys of fields with duplicates
Select the table in the Database window.
Click Copy on the toolbar.
Click Paste on the toolbar.
In the Paste Table As dialog box, type a name for the copied table, click
Structure Only, and then click OK.
Open the new table in Design view, and select the fields that contained
duplicates in the table you copied.
Click Primary Key on the toolbar to create a primary key based on the
selected fields.
Save and close the table.
To append only unique records to the new table
Create a new query based on the original table that contains duplicates.
In Query Design view, click Query Type on the toolbar and then click Append
Query.
In the Append dialog box, click the name of the new table in the Table Name
list and then click OK.
Include all the fields from the original table by dragging the asterisk (*)
to the query design grid.
Click Run on the toolbar.
Click Yes when you receive the message that you are about to append rows.
Click Yes when you receive the message that Microsoft Access can't append
all the records in the append query. This transfers only unique records to
your new table and discards the duplicates.
Open the table to see the results. When you're sure the new table has the
correct unique records, you can delete the original table and then rename
the new table with the name of the original table.

For Further Information :
http://office.microsoft.com/assistan...010345581033&C
TT=1&Origin=EC010227041033&QueryID=qUf5qLZCA0

Please let me know has this helped You...
Thank you...
Raghu...
This posting is provided "AS IS" with no warranties, and confers no rights.

  #4  
Old June 30th, 2004, 06:12 PM
Alice Dawson
external usenet poster
 
Posts: n/a
Default Deleting duplicate records

Thanks Raghu - that did exactly what I wanted!

Alice
  #5  
Old October 30th, 2004, 05:04 PM
Linda
external usenet poster
 
Posts: n/a
Default

This is the part that I do not yet understand:

Include all the fields from the original table by dragging the asterisk (*) to the query design grid.


Where is the asteric to drag it to the grid?

  #6  
Old October 30th, 2004, 06:46 PM
PC Datasheet
external usenet poster
 
Posts: n/a
Default

Open to the database window and the queries tab. Click on New - Design View.
Select your table for the query. The table will appear in the query window
and you will see the nasterisk at the top of the list of fields in the
table.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"Linda" wrote in message
...
This is the part that I do not yet understand:

Include all the fields from the original table by dragging the asterisk

(*) to the query design grid.

Where is the asteric to drag it to the grid?



  #7  
Old July 10th, 2006, 08:39 PM posted to microsoft.public.access.gettingstarted
Guest
external usenet poster
 
Posts: 12
Default Deleting duplicate records

Thank you, ive been trying to do this all day!!!

"Raghu Prakash" wrote:

Hi Alice,

Finding and deleting duplicate records in Access:
To delete duplicate records and keep the originals automatically
Deleting duplicate records from a table requires two steps. First, you
create a copy of the structure of the table that contains duplicates and
make primary keys of all the fields that contain duplicates. Second, you
create and run an append query from the original table to the new table.
Because fields that are primary keys can't contain duplicate records, this
procedure produces a table without duplicate records.

To copy the table and make primary keys of fields with duplicates
Select the table in the Database window.
Click Copy on the toolbar.
Click Paste on the toolbar.
In the Paste Table As dialog box, type a name for the copied table, click
Structure Only, and then click OK.
Open the new table in Design view, and select the fields that contained
duplicates in the table you copied.
Click Primary Key on the toolbar to create a primary key based on the
selected fields.
Save and close the table.
To append only unique records to the new table
Create a new query based on the original table that contains duplicates.
In Query Design view, click Query Type on the toolbar and then click Append
Query.
In the Append dialog box, click the name of the new table in the Table Name
list and then click OK.
Include all the fields from the original table by dragging the asterisk (*)
to the query design grid.
Click Run on the toolbar.
Click Yes when you receive the message that you are about to append rows.
Click Yes when you receive the message that Microsoft Access can't append
all the records in the append query. This transfers only unique records to
your new table and discards the duplicates.
Open the table to see the results. When you're sure the new table has the
correct unique records, you can delete the original table and then rename
the new table with the name of the original table.

For Further Information :
http://office.microsoft.com/assistan...010345581033&C
TT=1&Origin=EC010227041033&QueryID=qUf5qLZCA0

Please let me know has this helped You...
Thank you...
Raghu...
This posting is provided "AS IS" with no warranties, and confers no rights.


  #8  
Old April 9th, 2010, 08:58 PM posted to microsoft.public.access.gettingstarted
tim.hambly[_2_]
external usenet poster
 
Posts: 4
Default Deleting duplicate records

This is really great however I thought it was supposed to take the most
unique record to the new table. I am trying to do this same thing as I have
duplicates in my table however only one record out of each of the duplicates
has data entered into a certain field I need. When I run the append query I
am getting some records that has blank data in those fields instead. Is
there anyway to specifiy which duplicates it should remove when doing the
append query? Thanks,

"Guest" wrote:

Thank you, ive been trying to do this all day!!!

"Raghu Prakash" wrote:

Hi Alice,

Finding and deleting duplicate records in Access:
To delete duplicate records and keep the originals automatically
Deleting duplicate records from a table requires two steps. First, you
create a copy of the structure of the table that contains duplicates and
make primary keys of all the fields that contain duplicates. Second, you
create and run an append query from the original table to the new table.
Because fields that are primary keys can't contain duplicate records, this
procedure produces a table without duplicate records.

To copy the table and make primary keys of fields with duplicates
Select the table in the Database window.
Click Copy on the toolbar.
Click Paste on the toolbar.
In the Paste Table As dialog box, type a name for the copied table, click
Structure Only, and then click OK.
Open the new table in Design view, and select the fields that contained
duplicates in the table you copied.
Click Primary Key on the toolbar to create a primary key based on the
selected fields.
Save and close the table.
To append only unique records to the new table
Create a new query based on the original table that contains duplicates.
In Query Design view, click Query Type on the toolbar and then click Append
Query.
In the Append dialog box, click the name of the new table in the Table Name
list and then click OK.
Include all the fields from the original table by dragging the asterisk (*)
to the query design grid.
Click Run on the toolbar.
Click Yes when you receive the message that you are about to append rows.
Click Yes when you receive the message that Microsoft Access can't append
all the records in the append query. This transfers only unique records to
your new table and discards the duplicates.
Open the table to see the results. When you're sure the new table has the
correct unique records, you can delete the original table and then rename
the new table with the name of the original table.

For Further Information :
http://office.microsoft.com/assistan...010345581033&C
TT=1&Origin=EC010227041033&QueryID=qUf5qLZCA0

Please let me know has this helped You...
Thank you...
Raghu...
This posting is provided "AS IS" with no warranties, and confers no rights.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to identify duplicate records and count them vishal Worksheet Functions 1 June 6th, 2004 06:33 PM
duplicate records, how to identify and count Andy B Worksheet Functions 2 May 26th, 2004 02:57 PM
Determine duplicate records singkit Worksheet Functions 2 March 23rd, 2004 05:52 PM
Duplicate Records Jas Worksheet Functions 4 January 28th, 2004 10:00 PM
Deleting duplicate records danldata Worksheet Functions 2 December 11th, 2003 11:03 PM


All times are GMT +1. The time now is 11:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.