• Zip Code Radius Tool |
• About Eraserve AP |
• MS Access Tutorials |
• MS Access VBA

Back To Top

Simple MS Access create Table tutorial for 2000, 2002, 2003, 2007

Pretty much in all versions, from Access 97 to 2007, the process is similar. There are of some visual differences. For the most part, this article will be dealing with 2000-2003.
Go here for MS Access create Table tutorial in PDF Format

Go here to Create a Table in VBA
Go here to Create a Sample Table in VBA

Assuming you have New Database aptly named db1.mdb by MS Access we will go through designing a basic table in Design View, and set a few of the data types. If you do not right click an empty folder and select "New/Microsoft Office Access Application" or Start MS Access now and Select File/New and click Blank Database.

To design the table we will click "Create table in design View" which will bring up a new window. The default name will be Table1. This window will have 3 columns: Field Name, Data Type and Description. In the First column you must input your chosen field names. The following characters are not useable: Periods, Exclamation Points, Brackets, non-printable Characters and leading spaces. After you put in a field name (Mine is "CNT" for Count) you can click the empty box under Data Type. You will be presented with a drop down list of the types available. I have selected "AutoNumber" as this will be a useful data type I can use as a Unique Record Number or Primary Key. Primary keys must be unique. You may then enter a description of the field in the Description column. This can be anything you like. For example, "Unique ID for customers" would let people who use your database know what the field is for.

Once the Data Type is set, the General Tab below the columns will then populate with the information related to your data type. We need not change anything for the AutoNumber field. Notice that all new fields default to "Indexed No". Fields that will be searched or joined to others should be set to "Indexed Yes (Duplicates OK)" or "Indexed Yes(No Duplicates)" accordingly. Large databases that have not been indexed as such will receive a performance increase once this is done. I will input the following fields CONTACT, ADDRESS, ADDR2, CITY, STATE, ZIP, Zip5, and Zip4. I will set CNT as Primary Key (right Click and Select Primary Key, there must be no duplicates in this field) which will automatically Index the field as "Yes(No Duplicates)". I will then Index STATE and Zip5 as "Yes (Duplicates OK)". Zip5 Field size will be set to 5 ensuring there are only 5 digits in the field. STATE Field size will be set to 2 as we will be using 2 digit state abbreviations. All fields except the CNT field are set to text. Some may think we should set the Zip5 and Zip4 fields to Number, however setting these fields to Number will remove all leading Zeros of the Zip Codes so Text it is. Once you have all your fields you can close the Table. MS Access will prompt you to save and ask for a Table name. You can leave Table1 as the name or change it as you see fit.
Once you have saved your table you can add or delete fields at any time by Right clicking the table and selecting Design View.

Now if I have a Database with a few Hundred Thousand Contacts in a "Customers" table and a Table with a few hundred "Sold products" I can join the two by the "CNT" fields. My "Customers" Table will have the "CNT" field as Primary key (No duplicates allowed in Primary Keys) and my "Sold Products" Table will have a "CustomerID" field populated from the Customers table "CNT" field. The "CustomerID" field will be indexed as "Yes (Duplicates OK)". Once the two fields are joined I can then create another table from the two and Count how many products a customer has bought. I could include zip codes or specific Zip codes, States or specific States ect... We could even remove duplicates from our Customers Table with the fields we have created.

For further help or suggestions go here: MS Access tutorials, Tables, Queries and later: Forms

You can add a few records to the Customers table and then create a "Sold Products" table with an ID, CustomerID and DESCRIPTION Fields. Later we will discuss some queries involving the two tables.

You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.

For further MS Access tutorials go here: MS Access tutorials