Change data type & values of related fields

[Hoving]

I have two tables, a table of teachers and a table of students. The tables are linked by TeachID.

tblTeachers tblStudents
---------------------- -------------------
TeacherLast (text) StudentFirst
TeachID (PK) (autonum) StudentLast
TeachID (num)

In tblTeachers, I need to convert the TeachID field to a text field. Consequently, the TeachID field in tblStudents will need to be converted to text. Once done, I need to update the TeachID field for records in tblTeachers with certain values, and (with Cascade Update allowed?) have the new values automatically cascade up for the matching records in tblStudents.

How can I do this without losing the relationships between the records?

Thanks. Kurt

 

[SgtJarrow]

First break the relationship via the relationship window. Next change the datatypes of the teachID field in each table to text. Next recreate the link via the relationship window. Check the box to enforce referential integrity and the box to cascade updates. Then go to the teacher table and change the teachID as necessary.

If you do this, the TeachId in the student tables will be updated along with the teachID in the teacher table due to the cascading of updates...

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com