Access 2007 Tables

How Access stores data in Tables

Like all other databases, Access 2007 stores data in tables. They look a lot like the cells of a spreadsheet with columns and rows. Each horizontal column represents a table record, and each vertical column represents a table field. See Table example below:

A simple Table

In the example Table above, there are four fields containing information about an individuals ID number, first name, surname and age. Below the field headings there are 3 records containing information or data for each individual. As such, a database table is a list with each column containing the same specific sort of information. Each row of information is an individual record that might relate to a particular person, a business, or a product etc.

When planning a database table, most database designers will decide which column headings or fields they are going to use. This is the basis of the table structure. The actual data is added later and is not a part of the design process.

Create Access Table

1. Open database file

If you created a blank Access database in the previous tutorial lesson, you are now ready to create a new table. Begin by opening your existing database file if it is not already open. Do this by clicking on the Access desktop icon to bring up the getting started screen again. You should see the file name that you just created towards the top of the right hand side bar (If you cant see it, click the folder icon to browse for the file). Click on the file name to bring up your blank database.

If, at this stage, you get a security warning underneath the ribbon, click where it says options, select the "enable this content" radio button (in the pop-up window), and then click ok.

Your blank database file should now be fully open.

2. Create Access Table

Select the CREATE tab on the Access ribbon. Next select the TABLE DESIGN icon from the TABLES group. This creates a new table.

3. Create fields in DESIGN VIEW

This brings up the TABLE DESIGN GRID where you enter each field name and its data type. The first field we are going to create is the ID field which is going to contain a unique reference number for each record. Enter the name "ID" into the first column of the first row in the grid. Because we want Access to automatically generate a unique reference number, select AUTONUMBER from the drop down list in the data type column. You can also enter a description for each field, but this is not essential.

On the next row the field is going to be called FIRSTNAME and the data type is going to be TEXT. On the third row the field name is SURNAME with the data type again being TEXT. And finally, the last field name is AGE and the data type here is going to be NUMBER.

Before you save the table you will need to choose the Primary Key, which in this case is ID. To do this, select the ID row by clicking on it, and then simply click the primary key symbol on the Ribbon.

The table design grid should now look like this:

You can now save the table by clicking the save icon on the top left of the screen above the Access Ribbon. To view your table select DATASHEET VIEW from the VIEWS group under the DESIGN TAB. This brings up the datasheet view of the table that you have just created. You should see your field headings running across the top of the table.

4. Data entry in DATASHEET VIEW

Although entering data onto the datasheet table is not a part of the design process, the table now exists as a database object, and you can test it by entering some information into the cells.

Select the first cell in the FIRSTNAME column and enter the name JOHN. For SURNAME enter JONES, and for AGE enter 35.

NB just ignore the column underneath the heading ADD NEW FIELD. We created all the fields we needed in design view.

You have now entered the first record in the table - record 1 for John Jones aged 35. You can now press the return key and the record will save automatically. You are now ready to enter the second record on the next row - TRACEY SMITH 25. Press return and then fill in the data for the last record we are going to do for now - ANNE McNEIL 30

Your datasheet table should now look like this:

Working with Access Tables

Once you have an Access Table populated with data, you have the option to work with it right away. Indeed, some people may not go on to create forms, queries, and reports once they have created their first table. With a single table you can, for example, store, edit, filter, search, and calculate a set of database records. Now that you have created your first table and stored a number of records, we will take a look how you go about working with tables. We shall use your newly created table with some additional records to illustrate how to do this.

Feel free to download the populated database table or input the extra records manually so you can try out the features yourself. This is the fully populated table we shall be working with:

Follow the links below to learn about working with Access Database Tables

Once you have finished learning about creating and working with tables,the next section of the tutorial is about Access Forms.