How to: Create the Structure of a Table Using Design View in Microsoft Access 2003

Creating a table using Design View in Access 2003 consists in explicit defining the data types […] associated to the fields and their properties.
Table EMPLOYEES is considered, having the following structure: IDAng, DenDep, Nume, Pren, Functia, AdrEmail, TelInt, Adresa, Loc, Jud, CodPost, TelAcs, TelJob, DataNast, DataAng, Obs.


Fields of the table EMPLOYEES have the following meanings, data types and associated properties:

  • IDAng (employee ID): type Autonumber, changed properties Caption: Employee ID, Indexed: Yes (No Duplicates);
  • DenDep (department name): type Text, changed properties Caption: Department Name, Allow Zero Length: No, Indexed: Yes (Duplicates OK), Unicode Compression: No;
  • Nume (employee’s name): type Text, changed properties Caption: Employee Name, Allow Zero Length: No, Indexed: Yes (Duplicates OK), Unicode Compression: No;
  • Pren (employee’s surname): type Text, changed properties Caption: Employee Surname, Allow Zero Length: No, Indexed: No, Unicode Compression: No;
  • Functia (staff position within the organization): type Text, changed properties Caption: Position hired, Allow Zero Length: No, Indexed: No, Unicode Compression: No;
  • AdrEmail (employee’s e-mail address): type Text, changed properties Caption: E-mail, Allow Zero Length: No, Indexed: Yes (Duplicates OK), Unicode Compression: No;
  • TelInt (extension number phone): type Text, modified properties Size Field: 30, Caption: Extension Phone, Allow Zero Length: No, Unicode Compression: No;
  • Adresa (employee’s address): type Text, modified properties Field Size: 255, Caption: Employee Address, Allow Zero Length: No, Unicode Compression: No;
  • Loc (place of residence): type Text, changed properties Caption: City home, Allow Zero Length: No, Unicode Compression: No;
  • Jud (county home): type Text, changed properties Caption: County home, Allow Zero Length: No, Unicode Compression: No;
  • CodPost (postal code of residence): type Text, changed property Field Size: 20, Caption: Zip, Allow Zero Length: No, Indexed: Yes (Duplicates OK), Unicode Compression: No;
  • TelAcs (home phone number): type Text, changed property Field Size: 30, Caption: Phone home, Allow Zero Length: No, Unicode Compression: No;
  • TelJob (phone number at job): type Text, changed property Field Size: 30, Caption: Job telephone, Allow Zero Length: No, Unicode Compression: No;
  • DataNast (birth date of employee): type Date/Time, modified properties Format: Short Date, Input Mask: 99.99.00;0, Caption: Date of birth;
  • DataAng (employment data): type Date/Time, modified properties Format: Short Date, Input Mask: 99.99.00;0, Caption: Employment Data;
  • Obs (observations regarding the employee): type Memo, changed properties Caption: Observations, Allow Zero Length: No, Unicode Compression: No.

Significance of modified properties are:

  • Caption: label field to use in a form;
  • Indexed: it allows the values indexing to increase the speed of search and sorting them;
  • Allow Zero Length: it secures permission/restriction on the existing field;
  • Unicode Compression: it secures permission/restriction unicode compression of a field;
  • Field Size: maximum number of allowed symbols;
  • Format: template for displaying the value of a field;
  • Input Mask: input template of a value in a field.
Create structure of table EMPLOYEES in Design View
Create structure of table EMPLOYEES in Design View

IDAng field is primary key of the table. Primary key of a table consists of one or more fields are used to identify a single record in a table.
A record of a table represents the set of values associated with the set of fields that defines the structure of the table. For EMPLOYEES table, the record contains the typical data of an employee. Number of records is equal to the number of employees of the organization.
In EMPLOYESS table, to establish the field with primary key role, it is done so:

  • IDAng field is selected;
  • contextual menu is activated and command Primary Key is selected; another possibility is to select the icon of the Primary Key from toolbar.