Title

Need Recommendations on setting up new table imported from main database table

Post

My main study database table has about 70 fields, of which includes last name and first name of the investigator. Each investigator has multiple study records and that requires duplicate entry of investigator name. I decided to created a new table with investigator info and imported those records, removed the duplicates and created a serial number for each investigator as well as a Uniquename (LastName & FirstName). My question is do I delete the investigator name form my main study database and how do I link each study record to the correct PI? And for future entry of new investigators, what is the best method do that, a validated list and new entry option?

The new table, and using an ID, to use from now on, is a good thing. Right now however, you need to use the existing data, to go get the new IDs. That is, first you need to create a relationship based on "name" to "new table with name". [ If you have a calculation field for those "unique name", you might want to add it (as a new calculation field), in the existing table; if it can help match your new "clean names".]

Once you've got as many matches as you can, enter the "ID" field (added to existing table's layout with the old names), and use a single script step, Replace Field Contents, using the "Replace with calculation" option, matching the relationship above.

Be sure to BACKUP your file before trying the above. Replace Field Contents can be dangerous. In this case however, you're IN the new blank ID field, so there's no data to damage :0

Then, after success, you could just switch the above relationship, to use ID = ID, as you've now got the new one on both sides. You could then either switch the First & Last name fields to be either: Lookup the name, OR, change to a calculation field, looking at the new (unique) name table, to see the names {this may still be useful to have in the table}, or you could just delete those fields. [ However, make sure that another else looking at them can see the data from the new one {if not, the calculations above may still be useful}.]