The main table is AWARDS. The related tables are PERSONNEL and DOCUMENTS. The primary key is the field LOG NO.

When an AWARD record is created, a temporary primary key is assigned, TEMP100. Related PERSONNEL and DOCUMENTS records are created.

A few weeks later, a permanent LOG NO.(primary key) is assigned to the AWARDS file.

Q: How do I change the primary key (LOG NO.) in all the related records when I change the primary key in the main (AWARDS) file?

Right now I am struggling with a lengthy script that uses a global field (gOldKey) and finding each record in the related tables and then executing a REPLACE command.

Thanks for your help!

The work-flow is not entirely clear: it sounds like once you've created a new Award record, you cannot create any more new records for a few weeks - until someone(?) assigns a permanent number to the Award.

Why not use auto-entered serial numbers for the primary key - permanently. Then, when you do get the permanent number, just enter it into a field as another attribute of the parent Award.

Wouldn't that mean that the value of the key in the related tables (e.g., PERONNEL) would have to be the same value as the key (auto-entered serial number) in the main (AWARDS) table?

How would the serial number from AWARDS pass to the key field in the table, PERSONNEL?

Are we saying that we pass the auto-entered serial number in AWARDS to a field (not the serial number) in the PERSONNEL file?

Thanks...

I am not sure I understand your question: the value of the foreign key in the child table/s would naturally have to be same as the value of the parent primary key. In that aspect nothing would be changed. The value would be passed to the child in the same way it is now. The only thing changed is that the yet unknown number is not used at all in the relationship. It's just another field in the parent (Awards) table.

Problem solved! How do I delete all of these postings that I created?

Please [color:red]don't Delete any posts.

If there are duplicate posts, we usually take care of that. The rest of them should be left right where they are, so that other members with a similar problem, can learn from them.

Lee