Access 2007 - Creating Primary Keys

Resolved · Medium Priority · Version 2007

Tue 30 Mar 2010, 16:37 Delegate Martin said...

Martin has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Excel Introduction course
Excel Intermediate course
Excel Advanced course

Access 2007 - Creating Primary Keys

Hi,

Using Access 2007/Windows 7 and looking to create my first working database.

Having spoken to Anthony during my intermediate course, it feels as though the Primary Key in my initial contacts Table will be a unique identifier code relating to individuals - something like 2-4 letters at the start followed (upto say 4) digits. E.g. NOTT01 or OFF0150.

So my first question is:

Should I create the unique identifier code as a single field to contain all the necessary text and number, or would it be better to split the unique identifier into its two elementsin two fields? E.g:

CATEGORY of Person (E.g. NOTT or OFF) and NUMBER within the category

Also, if it were split into two fields, is there a way of making the number a separate AutoNumber for each different category of Person or is this a hope too far do you think?

Many thanks

Martin Parker

Wed 7 Apr 2010, 16:24 Trainer Simon said...

RE: Access 2007 - Creating Primary Keys

Hi Martin,

Thank you for your question.

Sorry for the delay in responding.

I suppose the answer depends on whether the entire mix of letters and numbers will be completey unique for each individual which means just having one Primary key holding all the information.

Is all the information held in one table?

Do you have a separate table for the employee and other tables holding the list of categories and numbers?

If you split the fields, the primary key must be the part of the code that is unique to each individual. The second field could be set up so you enter extra info such as Dept. for each individual.

You can then create a query to concatenate the two fields into a new field maybe for reports.

I hope this helps.

Regards

Simon

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Access tip:

Hiding Multiple Table columns

If you want to hide non-adjacent Access table columns. In Datasheet view, open the table that contains the columns you want to hide.

On the Format menu, click Unhide Columns.
In the Unhide Columns dialog box, clear the check box next to the name of each column you want to hide.
Click Close.

This method makes having to use the Hide Columns command repeatedly unnecessary

View all Access hints and tips