MS Access Table Design Tutorial for Versions 2000, 2002, 2003, 2007

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 sideof 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 numberdepending 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 tablesshould be indexed to improve performance Primary Keys
automaticallyset 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 "FieldProperties" 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.