MS Access Basic Tables For MS Access 2000, 2002, 2003
Pretty much in all versions, from Access 97 to 2003, the process is just about the same. Tutorials for MS Access 2007 may come at a later date because of some visual differences. Once you have a new Table in Access 2007 then you should be able to follow along. For those most part this article will be dealing with 2000-2003.
|
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. 
|
|
To design the table we will click "Create table in design View" this will bring up a new window. Default name will be Table1. This window will have 3 columns: Field Name, Data Type and Description. The First column you 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 selected "AutoNumber" as this will be a useful data type I can use as a Unique Record Number or Primary Key (More on this later). 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.
To make this easier please
use the fields I do. You are able to add or delete fields at any time once we are done.
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.