Extracting duplicates

[John Marshall, MVP]

Any suggestions on how to modify a table that has a text field that has many
duplicates to replace the text field with a pointer to a new table that
contains unique values of the text field?

For example: A table of students has a text field with the full name of the
teacher. Change the table so that it contains a field of teacher ids rather
than teacher names and create a table of teachers with the new teacher id.

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm

 

[Nikos Yannacopoulos]

John,

Access has a built-in wizard to help you do this. Try menu Tools > Analyse >
Tables.
Alternatively:
Make your teachers table first (easiy way: select query on main table, just
teacher name field, Totals > Group By and make it a make table query).
Assing a key value to each record (your Primary Key).
Then make a query joining the main table and the teachers table on the
teacher name (this is just temporary), and change it to an update query, set
Update to in the main table's teacher name field to the name of the PK field
in the teachers table (enclosed in square brackets) and run. You're almost
there, the only other thing to be done is to go to relationships and create
one between the two tables. Also, it would be wise to go to the main table's
design and change the size of the Teacher_ID field to save space.

HTH,
Nikos

 

[Guest]

Create a tblTeachers table and populate all the unique names (from your Students table) as both the TeacherName and TeacherID.
Create a One-To-Many relationship with the Students table and enforce referential integrity.
In the tblTeachers table, change the names in the TeacherID field to IDNumbers (or whatever format you're going to use). The changes should cascade down to the Students table.

Hope this helps!

Howard Brody



----- John Marshall, MVP wrote: -----

Any suggestions on how to modify a table that has a text field that has many
duplicates to replace the text field with a pointer to a new table that
contains unique values of the text field?

For example: A table of students has a text field with the full name of the
teacher. Change the table so that it contains a field of teacher ids rather
than teacher names and create a table of teachers with the new teacher id.

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm

 

[Immanuel Sibero]

Hi John,

Just a note, the above posts would work assuming a 1-to-Many relationship
between Teacher and Student. Otherwise, a third (junction) table would be
necessary. In your Student table, in addition to duplicate teachers, are
there duplicate students? If so, then it's a Many-to-Many relationship which
requires a junction table.

Immanuel Sibero

 

[John Marshall, MVP]

The actual items are not students and teachers, but the example does convey
the point. In this "school" each student only has one teacher.

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm

 

[John Marshall, MVP]

Thanks

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm

Create a tblTeachers table and populate all the unique names (from your

Students table) as both the TeacherName and TeacherID.

Create a One-To-Many relationship with the Students table and enforce referential integrity.
In the tblTeachers table, change the names in the TeacherID field to

IDNumbers (or whatever format you're going to use). The changes should
cascade down to the Students table.

 

[John Marshall, MVP]

Thanks

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm

 

[PC Datasheet]

John,

Create a make table query that only includes the teacher in the students table.
Before you run the query, right click in the window (where the student table is
shown) any where in the gray area of the query grid. In the dialog that appears,
click on properties. Set the Unique Values property to Yes then run the query.
You should now have a table containing the unique list of teachers with a
TeacherID in each record.

Next open the student table in design view and add a new field named TeacherID.

Now create a new query that includes both the student table and the teacher
table. Join the teacher name field in both tables. Pull down TeacherID from the
student table. Convert the query to an update query. Where it says Update To
under TeacherID type in TblTeacher.TeacherID. Run the query. You will now have
TeacherId in the student table filled in with the TeacherID value that
corresponds to the teacher name from the teacher table. You can now delete the
teachername field from the student table.