Introduction to Tables
By default your new Database will include a Table when it is first created. If you would like to create another table you can do so by clicking on the Create tab and selecting either Table, Table Templates, or Table Design.
- Table : Creates a new table in Datasheet View
- Table Templates : This option actually opens up multiple options of pre-designed tables. These tables are Contacts, Tasks, Issues, Events, and Assets. These pre-designed Tables are there to aid you in the creation of common tables.
- Table Design : Essentially the same thing as just creating a Table but this option will put you directly in Design View. This allows you to begin customizing the Fields immediately.
Creating a Table in Design View
For the sake of example we will be working in Design View. Work on the table that is created for you by default by selecting it in your Navigation Pane and switching to Design View from the Home Tab. You can also create your own table in design view. When you switch to Design View it should ask you to name your table. For the purposes of this tutorial it doesn't matter what you call your table.
Field Name: On the left side you enter all the fields that you would like for your table. This includes things like First Name, Address, and Phone Number. You may notice that First Name is used instead of just Name, this is because Access uses the field name as a reserved field.
Data Type: This column is where you set the type of information this field will contain.
- Text - the default data type, maximum of 255 characters per field.
- Number - Any number can be stored, specific number types can also be specified.
- Date/Time - A date, time, or combination of both
- Currency - Used when the field represents monetary values. It can be set to include a currency symbol and decimal or comma
- AutoNumber - When a new record is created, Access will automatically assign it a unique integer. You can specify the increment by which the numbers are assigned or whether a random number should be selected in the new Values field of the general tab at the bottom. Since each record must include at least one field that is unique and cannot be replicated, AutoNumbers are an easy way to fulfill this requirement.
- Yes/No - Use this data type for values that can only be one or two options such as True/False, Yes/No, or On/Off
- OLE objects - An OLE (Object Linking and Embedding) object is something that was created in another program. This can include images, sound files and Word or Excel files.
- Hyperlink - A hyperlink allows you to create a link to an Internet or Intranet site, or another place in the database. The link should be entered using the following format:
The Address is the only required part of the String. Examples
Within a database: TableName#C:\My Documents\database1.mdb#newTable
Description: This is optional. A brief description of the contents of the field. It is particularly useful if more than one person is using and editing the database.
Field Properties: Choose properties for the selected field from the bottom pane.
As mentioned before each table must have at least one field that is the primary key. This primary key is used to differentiate between multiple records. An example of a primary key might be something like a phone number, as long as no one is from the same house. The primary key must be unique for each record. Because phone numbers are unique for each person, you are guaranteed that you will not have any repeats. Another good example of a primary key would be a social security number. If you cannot think of some field that would be unique for each record, it is a good idea to create some sort of ID field and give it an AutoNumber data type.
To establish a primary key in Access 2007 you must be working on your Table in design view. First, you must create some field that you know will always be unique, for the sake of our address book example let stick with phone number. Once you have entered your Field Name and Data Type, select your field. With the field selected, click the Primary Key button in the Design Tab. You will see a small key appear to the left of the Field Name. By default, Access will establish a filler primary key labeled ID. When you set your Primary key to Address, ID will lose its Primary key status as you can only have one Primary key per table.
Once you have completed setting up your table with the appropriate field, you need to actually input the data or records. You need to switch to Datasheet to do this so do that first. Once in Datasheet view you can enter new records by inputting data on a new row. New records can be entered on the row marked by the * symbol on its far left. Complete each field for the record and move between them by hitting the tab key to move forward and shift + tab to move backward. You can also change a records field by selecting it with your mouse.
To edit records, you just need to select the record you wish to change and select it with your mouse. You can also navigate records with the arrow keys on your keyboard.
To delete records, select the unwanted record and hit the delete key.