Basic Design Question

[shaunk]

I have inherited an Access database that was originally built in Access 97 and I have converted to Access 2003. It has a main table (Archive_Register) and several lookup tables used in forms but not defined in relationships. I am not going to setup relationships because there are many records that won't meet the foreign key criteria (the data is in a mess).

In any case, there is a natural key for the Archive_Register table, being MRN. However, the users want to be able to add records without entering MRN, but if they do, it must be unique. So I cannot use MRN as the primary key. I have added code to check for MRN uniqueness.

The Archive_Register will not be joined with any other table.

1. Is there any real advantage to introducing an autonumber field and set that as the primary key, just for the sake of having a primary key?
2. If I index the MRN field, Yes(No Duplicates), will the nulls in the MRN field count as duplicates i.e will I lose those records on creating the index.

Thanks


The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[Remou]

Nulls don't count in 2000 and think that 2003 is the same in this respect.

 

[maxhugen]

1. Is there any real advantage to introducing an autonumber field and set that as the primary key, just for the sake of having a primary key?

In your situation, where you have one main table and no relationships, there won't be any advantage to you.

2. If I index the MRN field, Yes(No Duplicates), will the nulls in the MRN field count as duplicates i.e will I lose those records on creating the index.

You won't 'lose' data... when you try to create the index, Access will check the existing data, and warn you that it cannot create the unique index if there are duplicates. Nulls are ignored in 2003, as Remou suggested.

Max Hugen
Australia

 

[Remou]

What I suggested was that a unique index is permitted with nulls, that is "Yes (No Duplicates)". It is best to ensure that Allow Zero Length Strings is set to No. A primary index is not permitted.

 

[shaunk]

I've set the MRN field to be indexed (No Duplicates) and haven't lost any records...as the posts state.
I have found, however, that the introdution of an autonumber field helps with the creation of comboboxes that are used to find records on a form.

The risk with keeping an open mind is having your brains fall out.
Shaunk