Newly added key

M

Mark

There is a database developed with Access 97 for a number of years.

There is a request to add the "Position" field in the main table. I would
like to know what is the best way to do so ?

Should I create a new table named "Position" with PostionID Key and Position
Description fields. Add an additional field in the Main Table that is
referenced by the PositionID Key ? If it is the proper way, should I impose
Referential Integrity ? I suppose not as I don't know how to handle those
existing thousands of records that without that field.

Your advice is highly appreciated.
 
B

Brendan Reynolds

Referential integrity means that there can not be a record in the Main table
with a value in the PositionID field that does not exist in the Position
table. It does not mean that there can not be a record in the Main table
with a Null value in the PositionID field (provided the Required property is
not set).

You can either ...

1) Leave the Required property of the PositionID field in the Main table set
to 'No' and leave it Null in the existing records.

2) Create a record in the Position table to represent unknown or unavailable
position information, e.g. PositionID = 1, Position Description = "Unknown",
or "N/A", or whatever. Run an update query on the Main table to set the
PositionID field in existing records to the value of the corresponding
PositionID in the Position table (1 in the example above).
 
M

Mark

Dear Brendan,

Thank you for your advice. I prefer the first option as I believe that end
users don't like to show "Unknown" / "N/A" (I will confirm with him).

Re the first option, is it necessary for me to specify that it is NULL ? If
it is a Long Integer field (With reference to the Key in the Position Table)
, can we assign NULL to it ?

Thanks again for your help.
 
B

Brendan Reynolds

You don't need to explicitly assign Null, just don't assign anything. One
thing to watch out for, though, is that when you create a numeric field
Access will automatically give it a default value of 0 (zero). In form
design view, delete that 0 from the Default Value property of the new field.
 
Top