Good Day!
I am working on an audition tracking database that was implemented before I stared working here. It was originally designed poorly with no normalization and each audition got a new record regardless if that individual has taken one before. I have since split it into five tables.
For my Student Table I have;
LName
FName
MInit
ID
By migrating the data I have about 2000 duplicate students.
My Audition Table is
ID
studID
AuditionDate
Remarks
scores...
I would like to find a way to run a query to find duplicates (easy) and to change the duplicates in the Auditon table. For example;
I would query tblStudents and get;
Doe, John P 654
Doe, John P 7822
Doe, John P 1382
how to I update tblAudtions so that 7822 and 1382 are now 654?
Full Version: Update Query Based On Duplicates
CplFish,
What is your first name?
To remove duplicate records from a table, follow these steps:
1.Make a copy of the structure of the table from which you want to remove the duplicate records.
To copy a table:
•Select the table in the Database window
•On the Edit menu, click Copy.
•On the Edit menu, click Paste.
•Enter a name for the new table.
•Select Structure Only
•Click OK.
2.Open the new table in Design view.
3.Select the field(s) that contain the duplicate values.
4.To make your selection the primary key for the table, click the Primary Key button on the toolbar.
5.Save and close the table.
6.Create an append query based on the original table containing duplicates.
7.In the query Design View, on the Query menu, click Append Query.
8.In the Append dialog box, click the name of the new table from the Table Name list, and then click OK.
9.Include all the fields from the original table by dragging the asterick (*) to the query design grid.
10.On the Query menu, click Run.
11.Click Yes in the dialog box advising you that you are about to append records.
12.Because the Primary Key field(s) in the new table will not accept duplicate values, the following error message will be displayed:
13.Click Yes.
14.View the contents of the new table. When you're sure the new table has the correct unique records,
you can delete the original table, and then rename the new table using the name of the original table.
HTH
What is your first name?
To remove duplicate records from a table, follow these steps:
1.Make a copy of the structure of the table from which you want to remove the duplicate records.
To copy a table:
•Select the table in the Database window
•On the Edit menu, click Copy.
•On the Edit menu, click Paste.
•Enter a name for the new table.
•Select Structure Only
•Click OK.
2.Open the new table in Design view.
3.Select the field(s) that contain the duplicate values.
4.To make your selection the primary key for the table, click the Primary Key button on the toolbar.
5.Save and close the table.
6.Create an append query based on the original table containing duplicates.
7.In the query Design View, on the Query menu, click Append Query.
8.In the Append dialog box, click the name of the new table from the Table Name list, and then click OK.
9.Include all the fields from the original table by dragging the asterick (*) to the query design grid.
10.On the Query menu, click Run.
11.Click Yes in the dialog box advising you that you are about to append records.
12.Because the Primary Key field(s) in the new table will not accept duplicate values, the following error message will be displayed:
QUOTE
Microsoft Access can't append all the records in the append query.
Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add <number> record(s) to the table due to key violations,
0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway? To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.
Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add <number> record(s) to the table due to key violations,
0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway? To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.
13.Click Yes.
14.View the contents of the new table. When you're sure the new table has the correct unique records,
you can delete the original table, and then rename the new table using the name of the original table.
HTH
Thank you for your response Cynthia! My name is Chris.
I appreciate your guide on how to remove duplicates, however I'm trying to update the Child table so that when I remove the duplicates they point to the same record. So if A and B are duplicates, in my child table all records that were A and B point only to A.
I appreciate your guide on how to remove duplicates, however I'm trying to update the Child table so that when I remove the duplicates they point to the same record. So if A and B are duplicates, in my child table all records that were A and B point only to A.
Chris,
Create a new Indviduals table as suggested earlier
Make a copy of the new table --just a CYA
In query design
use the New Students table and the old iStudents table
where first name=firstname, lastname=lastname and middleinitial=middileinital
and the related table (Auditions?) that stores the IndvIDs that need to be updated
where the StudID = the OLD individuals table
Select Update as the type of query you are going to create.
Drag the StudID from the Related table to the query design grid
Update to the New Students table StudID
The other option is to do this manually:
use the query Wizard Find Duplicates
and include the IndvID in the results
then Select Make Table to create a separate table for the duplicates
Inserted two fields into this new table:
UpDtIndvID--data type Number
Delete-data type Yes/No
Manually go through this table and check Yes to Delete the dupe record/s
And c & p the ID which you want records updated
Then make a copy of the table that stores the records to update
Backup the database
Create an update query using the table I to update and this new Dupes table where IndvID = IndvID
Drag the IndvID from the table to update to the query design grid
Update to Dupes table InvID
Drag Delete from the dupes table to the query design grid
Criteria = True
Run the query
HTH
Create a new Indviduals table as suggested earlier
Make a copy of the new table --just a CYA
In query design
use the New Students table and the old iStudents table
where first name=firstname, lastname=lastname and middleinitial=middileinital
and the related table (Auditions?) that stores the IndvIDs that need to be updated
where the StudID = the OLD individuals table
Select Update as the type of query you are going to create.
Drag the StudID from the Related table to the query design grid
Update to the New Students table StudID
The other option is to do this manually:
use the query Wizard Find Duplicates
and include the IndvID in the results
then Select Make Table to create a separate table for the duplicates
Inserted two fields into this new table:
UpDtIndvID--data type Number
Delete-data type Yes/No
Manually go through this table and check Yes to Delete the dupe record/s
And c & p the ID which you want records updated
Then make a copy of the table that stores the records to update
Backup the database
Create an update query using the table I to update and this new Dupes table where IndvID = IndvID
Drag the IndvID from the table to update to the query design grid
Update to Dupes table InvID
Drag Delete from the dupes table to the query design grid
Criteria = True
Run the query
HTH
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.