Thank you for your post.
I'm sure others will pipe in and say "Don't ever change your key field".
Since most of your tables are linked by a key field, the trick is to find those records with the old value and change it to the new value. One way is to create a new field that will hold the new key, and then run a script which will find all records with the old key and replace it with the new key, and then change the original record key to the new key. Assuming your key field is titled "KEY", and you put the new value into a field named "NEW KEY", the script may look like the following:
Set Variable [ $old = KEY ]
Set Variable [ $new = NEW KEY ]
Go to Layout [ <layout with related information> ]
Set Error Capture [On]
Enter Find Mode 
Set Field [ <key field> ; $old ]
Perform Find 
If [ Get ( FoundCount ) > 0 ]
Replace Field Contents [ <key field> ; $new ]
Set Error Capture [Off]
Go to Layout [ <original layout> ]
Set Field [ KEY ; $new ]
Set Field [ NEW KEY ; "" ]
In essence, two variables are created to hold the new and old key values. We then switch to the layout of a related table and search for the old key. When found, replace the old key with the new key. If you have other related tables, do the same steps. When finished, return to the original record and update the KEY field with the new value and null the NEW KEY field.
TSGal is a very good prophet ;)
Don't ever change your key field.
The answer of TSGal is just enough to know why but I have another question: which is the advantage ?
Thanks for this very detailed solution. However, I guess I didn't phrase the question clearly. What I'm wondering is when I create the database (for collecting scores, grades and other academic performance stuff for a school), do I need to import the primary key into each table separately. Or, is there a way that the primary key can be propagated to all of the tables where is is necessary having imported it only once? There will be about 20 tables which will need to have student ID as the key.
Thanks again, and I apologise for the unclear question, though the solution is very useful, as it may come to that - our current system of student IDs is not always fool proof.
Actually, you phrased your question clearly. It wasn't clear to us what you were trying to accomplish! :-)
Seriously, a lot depends on how your tables are configured. Right now, I'm assuming you are importing information into the tables based upon the student name, and everything links together by that. Somehow, you will need a "key" field to relate to in your tables. For example, in your students table, each student will have a name (and soon, some kind of ID). In your classes table, you will have a link to the student table, a classID (subject), and a teacherID (who is teaching). If you just import information into these tables, you still need some kind of "key" field that allows you to connect them together.
I know I am giving you some vague answers, but if you have 20 tables, and you are importing data into each of these 20 tables and you want them related, then make sure you have some kind of "key" identifier/field.
Thanks for your patient response.
I have student IDs, but do I need them in all tables - the one common element? Or am I missing something?
I can't determine if you need the student ID's in every table. That is something you need to determine.
If you import the information, does it need to be connected to a student? If the answer is "yes", then you will need some kind of identifier to let you know it is linked to a specific student. If the records are unrelated to a student, then you don't need an identifier/key.
I have what I believe is the same question. I'm starting my first database of related tables.
Table 1: Organizations (primary key linked on foreign key in Table 2)
Table 2: Announcements (foreign key linked on primary key in Table 1)
The primary key field in Table 1 will be automatically filled with a unique serial number. Is there a way to automatically send the same number into the foreign key field in Table 2 when I enter a new announcement into the database? Does the link itself do that?
I guess you answered my question. Nearly every item (%, grade, class, tutor, etc) has to relate to a student eventually. I am dealing with student performance data.
Thanks, though i think auto-creation may be the best way to go in the end, rather than using our student ID system - manually generated and not infallible.
Thanks for your time and help.