How to Create a Table in Design View and Assigning Primary Key in Microsoft Access

Design view in Access is the best way to create tables, forms and reports. Design view lets you configure even the smallest option in your database. It provides you with various options for table construction. In addition to all the features on table tools field ribbon, it has many customizable field properties. Furthermore, we will teach you how to assign a primary key to your database. Primary key is the unique identifier of a specific table and it can be any field that is unique to that table only. Follow this step by step tutorial to learn how to assign a primary key in Access.


Step # 1 – Creating a table in Design view

In order to create a table in design view in Access, go to the create “tab” and click on the “table design” button. In this tutorial, we will construct the products table from scratch using the design view.

constructing a table using design view

Step # 2 – Entering fields and Renaming table

In the field name column, enter SKU and select its data type as text. Next, enter “products” and select its data type as text. In this manner, insert the fields you want in the table and their respective data types. After entering the relevant fields, enter their respective description right next to them.

After entering in all the fields and selecting their data type, we will save the table and rename it “products”.

giving name to a table and typing in information in fields

Step # 3 – How to make a field as primary key

After you have completed your “products” table, open the “customers” table and switch your view to “design” view. When you are in the design view, click on the grey box just before the id field and click on the primary key button to make it the unique field of that table. Primary key in Access ensures that there are no identical records in the table. Every record needs a unique identifier to distinguish it from other potential similar entries.

use of primary key for the first time

Step # 4 – Select field for primary key

After assigning the primary key to the customers table, we will on open the products table in design view again. We will select SKU as our primary key because it is the only differentiating factor as products change and so do price, cost and profit. Once again, we will click on the grey box next to SKU and click on the primary key button present in the ribbon area to make it our unique field for this table. As you press the primary key button, a small key will appear next to the specific field.

relevant field as primary key

Step # 5 – Making auto number data type as primary key

After assigning the primary key to the products table in ms access, we will now move to the sales reps table. The sales reps table distinguishing field will be the Worker Id field because only one number will be assigned to an employee. If that employee is deleted from the records, the number will not be repeated again. In auto number data type, a number is used only once so it can be used as an ID field but not good when used as a counter for the records.

assigning primary key to auto number field

In Microsoft access, you can create tables with ease. Assigning primary key is a very important part of making a database as it makes work easier for later stages. Queries, relationships and forms are all created on the basis of the primary key.