Access Database Tables

How Access Stores Data in Tables

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

A Simple Table Structure for Storing Personal Details
ID Number First Name Surname Age
1 John Jones 35
2 Tracey Smith 25
3 Anne McNeil 30

In the example Table above, there are four fields containing information relating to an individual - that is, the person's ID number (a unique reference number created automatically by the database to identify a persons record), 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.

Creating an Access Database 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 access launch screen. You should see the file name that you recently created in the right-hand side bar. Click on the file name to bring up your blank database. Alternatively, click the OPEN OTHER FILES icon at the bottom of the side bar to bring up the OPEN screen, and then click the BROWSE icon to find where you your database was saved.

    If, at this stage, you get a security warning underneath the ribbon, click ENABLE THIS CONTENT.

    Your blank database file should now be fully open as in the screenshot below:

    Open database

  2. Create the new database table

    Select the CREATE tab on the Access ribbon and click the TABLE DESIGN icon in the TABLES group. This opens the new table in DESIGN VIEW.

    Table Design Icon
    The TABLE DESIGN icon.


  3. Create database fields in DESIGN VIEW

    You should now see the TABLE DESIGN grid where we shall enter each field name and its data type - these are the table columns we are going to use.

    NB it may be worth mentioning at this point that the physical rows and columns of the design grid itself do not correspond to the rows and columns of the actual database table being created. In the DESIGN GRID, each row represents a field, and each column represents the field's datatype; but in the actual database TABLE, it is each column that represents a field and each row represents an individual record. As such designing the table is quite different from storing data in it once it is complete.

    Table Design Grid
    The TABLE DESIGN grid.

    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 design 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 of the grid, the field is going to be called FIRSTNAME and the data type is going to be SHORT TEXT. On the third row the field name is SURNAME with the data type again being SHORT 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 in the TOOLS group of the DESIGN Ribbon.

    Primary Key Icon
    The PRIMARY KEY icon.

    The TABLE DESIGN grid should now look like this:

    Table Design Grid
    The TABLE DESIGN grid.

    You can now save the table by clicking the save icon on the top left of the screen above the Access Ribbon (or FILE and SAVE via the Ribbon itself). When the SAVE TABLE dialog opens, enter “tblEmployees” for the table name.

    To view your table, select DATASHEET VIEW from the VIEWS group under the DESIGN TABLE. 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.

    The Datasheet View Icon
    The DATASHEET VIEW icon.


  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 You do not need to enter anything in the ID column as the ID number is generated automatically by Access.

    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:

    Entering Table Data
    Entering table data in DATASHEET VIEW.

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.

You might want to add the additional records (from ID No.4 onwards) before continuing:

The Employees Table
tblEmployees.

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

Edit Data
Filter Data
Sort Table
Search Table
Calculating Data

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