I am going to discuss MS Access Table Design in this post it will be detailed and lengthy. We will deal with basic data; each record will contain Name and Address information. We will then walk through the process of designing the Table or Tables as the situation may require. We will touch on Field Data Types, Primary keys and Indexing and field sizes. We could go about this in at least two ways. The first is to set up a single Table for our data in which we include the Name of the contact and their Address. A single table is simple and functional for smaller databases. The Second is to split the Names from the Addresses into two Tables each with a common field we can use to join them. Two tables will add a small level of complexity and is better for larger databases. The actual process of creating a Table will be similar for both methods and the process is similar in MS Access 97, 2000 and 2003. MS Access 2007 has some differences however once you have the initial table in design view you can follow along.
For a Basic Table Design with pictures see my MS Access Basic Table Design Tutorial.
Section One: Creating an MS Access Table
This section will have some commonalities for both the Single and the Two Table design process.
Part One: Creating the Table in MS Access
1) In an existing MS Access Database, under the Objects Menu of left hand side of the window, select Tables
2) Select Create Table in Design View
3) A new window will pop up called Table1: Table where 1 can be any number depending on current table names if any
Part Two: Creating Fields for an MS Access Table
1) The new Table Design window will have three columns: Field Name, Data Type, Description
2) Under Field Name you will enter the Names of the fields we want in our new Table
3) Enter in the following Fields:
a) RECNUM
b) CONTACT
c) ADDRESS
d) ADDR2
e) CITY
f) STATE
g) ZIP
h) PREFIX
i) FIRSTNAME
k) LASTNAME
l) SUFFIX
m) GENDER
n) ZIP5
4) In the Description column you can type a description for each field
5) Enter in the following descriptions for others who may view the Table:
a) RECNUM field: “Master Record Number for each contact.”
b) ZIP field: 9 digit zip code
c) ZIP5 field: 5 digit zip code
6) Select Data Types from the Data Type column, default is Text
7) Change the following Data Type:
a) RECNUM Field: Data type Autonumber
b) The rest can remain as Text
Zip codes you may think should be set to Data Type Number, this is incorrect. MS Access will drop all leading Zeros for any number, at least for Versions 97-2003. As for 9 digit Zip codes you have a – and the dash makes the number a string Text data type. MS Access 2007 may have a Data Type for Zip codes however Text works just fine in either case.
At this point you may wish to set Field sizes on the bottom half of the screen named “Field Properties” on the “General” Tab find Field Size. Text fields default to 50, you can enter in the appropriate size for ZIP (Set to 10, 9 digit zip code and the -), ZIP5 (Set to 5) and so on. Be aware that if you enter data whose length is longer than the field size, via Appends, Updates or otherwise, the data will be truncated to the field size.
Part Three: Setting Primary Keys and Indexing in MS Access
1) RECNUM field: Right click and select Primary Key
a) Note that the RECNUM field would be used to join your contacts to another table such as one called SALES
b) To Join fields in a Query they must be of the same Data Type (Autonumber is considered a Data Type of Number and is a Long Integer under Field Size)
c) You can select multiple fields as Primary Key, to do this Click one field, then hold the CTRL key and click another, while holding CTRL right click one of the fields selected and click Primary Key
d) Primary Keys must be Unique, meaning there can be no duplicate records within the Primary Key
e) Primary Keys automatically set Indexed to “Yes (No Duplicates)” attempting to change this would result in the removal of the Primary Key
2) ZIP5, CITY, STATE, GENDER and LASTNAME fields: Left click each one at a time
a) Per each field on the bottom half of the screen named “Field Properties” on the “General” Tab find “Indexed” its default is No
b) Change No to “Yes (Duplicates OK)” by clicking and selecting it from the list
c) Fields you will be searching through or using to join to other tables should be indexed to improve performance
At this point you can close your table. MS Access will then ask you to save if you have not already. MS Access will also ask you to set a Primary Key if you have not.
Hear the Single Table Design process ends.
Section Two: Creating Two Tables in MS Access to store related Data
You may want two tables in which both tables will have a field in common to tie the two together. In my case I will have one table for contacts and a second for their location.
Part One: The same as “Section One”, “Part One process” for both tables
1) In an existing MS Access Database, under the Objects Menu of left hand side of the window, select Tables
2) Select Create Table in Design View
3) A new window will pop up called Table1: Table where 1 can be any number depending on current table names if any
Part Two: Creating Fields for an MS Access Table
1) The First table I will refer to and eventually save as New_Contacts
2) Under Field Name you will enter the Names of the fields we want in our new Table
3) Enter in the following Fields:
a) RECNUM
b) CONTACT
c) PREFIX
d) FIRSTNAME
e) LASTNAME
f) SUFFIX
g) GENDER
Part Three: Setting Primary Keys and Indexing in MS Access
1) RECNUM field: Right click and select Primary Key
a) Note that the RECNUM field would be used to join your New_Contacts table to the second table I will call LOCATION
b) To Join fields in a Query they must be of the same Data Type (Autonumber is considered a Data Type of Number and is a Long Integer under Field Size) both tables will include the RECNUM field
c) Primary Keys must be Unique, meaning there can be no duplicate records within the Primary Key
2) GENDER and LASTNAME fields: Left click each one at a time
a) Per both fields on the bottom half of the screen named “Field Properties” on the “General” Tab find “Indexed” its default is No
b) Change No to “Yes (Duplicates OK)” by clicking and selecting it from the list
c) Fields you will be searching through or using to join to other tables should be indexed to improve performance Primary Keys automatically set Indexed to “Yes (No Duplicates)”
You would now want to save the New_Contacts Table.
Part Four: The Second Table in MS Access
1) The Second table I will refer to and eventually save as LOCATION
2) Under Field Name you will enter the Names of the fields we want in our new Table
3) Enter in the following Fields:
a) RECNUM
b) ADDRESS
c) ADDR2
d) CITY
e) STATE
f) ZIP
g) ZIP5
Part Five: Setting Primary Keys and Indexing in MS Access
1) RECNUM field: Right click and select Primary Key
a) Note that the RECNUM field would be used to join your New_Contacts table to the second table I will call LOCATION
b) To Join fields in a Query they must be of the same Data Type (Autonumber is considered a Data Type of Number and is a Long Integer under Field Size) both tables will include the RECNUM field
c) Primary Keys must be Unique, meaning there can be no duplicate records within the Primary Key
2) CITY, STATE and ZIP5 fields: Left click each one at a time
a) Per both fields on the bottom half of the screen named “Field Properties” on the “General” Tab find “Indexed” its default is No
b) Change No to “Yes (Duplicates OK)” by clicking and selecting it from the list
c) Fields you will be searching through or using to join to other tables should be indexed to improve performance Primary Keys automatically set Indexed to “Yes (No Duplicates)”
You would now want to save the LOCATION Table.
One thing to note that pertains to both methods: upon creating new fields on the bottom half of the screen named “Field Properties” on the “General” Tab find “Required”. Set this to No if you want to be able to leave fields blank when entering new data.
You should now be done Designing your Table or Tables in MS Access and are now ready to begin entering Data.
Tags: 
MS Access Table Design 
MS Access Primary Key 
MS Access Index Field
MS Access, Reference
ms access table design, ms access tutorials, ms access examples, ms access help, ms access