can the foreign key be updated ?

[jiehuang001]

I have a Patient and a Visit table. Each Patient has 0-n Visits, and the two tables are linked thought Patient_ID.

In the Patient table, there is a portal to display the Visit Information. However, in the Patient table, when I change the Patient_ID, the Paitent_ID in the Visit table is NOT updated automatically, therefore, this updated patient doesn't have visits any more while it had visits before the change.

Do i need to go to the visit table to manually update the Patient_ID there too???

Please help.

Jie

[RalphL]

Patient_ID should never be changed. This the primary key for the Patient table.

[toffler]

When constructing any type of medical solution, make the patient's ID# (primary key) a number field, auto enter serial number, increment by 1. Do not allow overide and require a unique value.

In the related tables, have foreign key, equal to the patient's ID#. The relationship between the two tables will be Primary Key = Foreign Key. They could both be named "Pt_ID_Number" for example. If your record system in the office has some medical record number for each patient and you want that number in the FMP database, then simply make a field for it, but don't use it in the relationship as mis-entry or change to that umber could cause your data to become corrupted. When you add a record in your related tables via a portal, the ID# (foreign key) will populate itself automatically.

Toffler

[Vaughan]

"When constructing any type of medical solution, make the patient's ID# (primary key) a number field, auto enter serial number, increment by 1. Do not allow overide and require a unique value."

Agree completely, except make it a text field. Text data is easier to do multi-keys (and other tricks) with than number data.

[Ender]

It is not necessary to use the "Unique" option on auto-entered serial numbers, and doing so can degrade performance on record creation in large files.