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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Deleting duplicate records
Thanks Raghu - that did exactly what I wanted!
Alice |
#5
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 04:52 PM |
Duplicate Records | Jas | Worksheet Functions | 4 | January 28th, 2004 09:00 PM |
Deleting duplicate records | danldata | Worksheet Functions | 2 | December 11th, 2003 10:03 PM |