Augustana College printing logo

About Access Tables

What Are Table Relationships

In a relational database, relationships enable you to prevent redundant data. For example, if you are designing a database that will track information about books, you might have a table called Titles that stores information about each book, such as the book's title, date of publication, and publisher. There is also information you might want to store about the publisher, such as the publisher's phone number, address, and zip code. If you were to store all of this information in the titles table, the publisher's phone number would be duplicated for each title that the publisher prints.

A better solution is to store the publisher information only once in a separate table, Publishers. You would then put a pointer in the Titles table that references an entry in the Publishers table.

To make sure that your data is not out of sync, you can enforce referential integrity between the Titles and Publishers tables. Referential integrity relationships help ensure that information in one table matches information in another. For example, each title in the Titles table must be associated with a specific publisher in the Publishers table. A title cannot be added to the database for a publisher that does not exist in the database.

Types of Table Relationships

A relationship works by matching data in key columns, usually columns with the same name in both tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table. For example, sales can be associated with the specific titles sold by creating a relationship between the title_id column in the Titles table (the primary key) and the title_id column in the Sales table (the foreign key). There are three types of relationships between tables. The type of relationship that is created depends on how the related columns are defined.

One-To-Many Relationships

A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, the Publishers and Titles tables have a one-to-many relationship: each publisher produces many titles, but each title comes from only one publisher.

A one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint.

In Access, the primary key side of a one-to-many relationship is denoted by a key symbol. The foreign key side of a relationship is denoted by an infinity symbol.

Many-To-Many Relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B. For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the au_id column (the authors table's primary key) and the title_id column (the Titles table's primary key).

One-To-One Relationships

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to:
• Divide a table with many columns.
• Isolate part of a table for security reasons.
• Store data that is short-lived and could be easily deleted by simply deleting the table.
• Store information that applies only to a subset of the main table.
In Access, the primary key side of a one-to-one relationship is denoted by a key symbol. The foreign key side is also denoted by a key symbol.

Benefits of using relationships

Keeping data separated in related tables produces the following benefits:

Consistency

Because each item of data is recorded only once, in one table, there is less opportunity for ambiguity or inconsistency. For example, you store a customer's name only once, in a table about customers, rather than storing it repeatedly (and potentially inconsistently) in a table that contains order data.

Efficiency

Recording data in only one place means you use less disk space. Moreover, smaller tables tend to provide data more quickly than larger tables. Finally, if you don't use separate tables for separate subjects, you will introduce null values (the absence of data) and redundancy into your tables, both of which can waste space and impede performance.

Comprehensibility

The design of a database is easier to understand if the subjects are properly separated into tables.

Import or link to create a table

You can create a table by importing or linking to data that is stored elsewhere. You can import or link to data in an Excel worksheet, a Windows SharePoint Services list, an XML file, another Access database, a Microsoft Office Outlook folder, and more.

When you import data, you create a copy of the data in a new table in the current database. Subsequent changes to the source data will have no effect on the imported data, and changes to the imported data do not affect the source data. After you connect to a data source and import its data, you can then use the imported data without connecting to the source. You can change the design of an imported table.

When you link to data, you create a linked table in the current database that represents a live link to the existing information that is stored elsewhere. When you change data in a linked table, you are changing it in the source. Whenever data changes in the source, that change is shown in the linked table. You must be able to connect to the data source whenever you use a linked table. You cannot change the design of a linked table.

You cannot edit data in an Excel worksheet by using a linked table. As a workaround, import the source data into an Access database, and then link to the database from Excel. For more information about linking to Access from Excel, search Excel Help, or see the links in the See Also section.

  1. On the External Data tab, in the Import group, click one of the available data sources.
  2. Follow the instructions in the dialog boxes that appear at each step.
  3. Access creates the new table and displays it in the Navigation Pane.

Creating a Table

A table is a database object that you use to store data about a particular subject, such as employees or products. A table consists of records and fields. Each record contains data about one instance of the table subject, such as a particular employee. A record is also commonly called a row or an instance. Each field contains data about one aspect of the table subject, such as first name or e-mail address. A field is also commonly called a column or an attribute.

  1. In the Database window, click on the Create tab.
  2. Click on Table Design
  3. Type a field name. Field names are best if keep short an avoid using spaces. Upper/lower case is OK.
  4. Press Tab to move to "Data Type." Select a Data Type.
  5. Note: The only time you would apply a number data type to a field is if you would possibly use the field in an equation (i.e.: a field called "Quantity" you might calculate later with a field called "Price" therefore you would make "Quantity" a number data type). Zip codes, phone numbers, social security numbers are always a "text" data type because they would never be equated. Most fields are Text data types.

  6. Type a Description. This is optional. By typing in information you are documenting your database. Your description will show up in the status bar in the table view (you must be on that field in order to see the description).
  7. For each field you enter, there are properties shown at the bottom of the screen.
    See "Field Properties" for more details.
  8. Click the Save button to save the table
    It is suggested that you type the letters TBL in front of your table name and avoid spaces. Click OK.
  9. To view your table, click on the View button.
  10. To return to the design view, click the same view button.

Primary Key

A Primary Key is assigned to a unique field. For example, EmployeeID would be considered a unique field because each employee will only be added to my table one time. By assigning EmployeeID as a primary key, Access will not allow me to enter the same employee ID twice. Primary keys are used when you create relationships between your tables. The relationships are necessary to update data in two tables at the same time. Other good examples of Primary Key fields are: Social Security Numbers, Inventory Numbers, Account Numbers, and Patient ID.

  1. In the Table Design view, Click on the field you wish to make the Primary Key.
  2. Click the Primary Key button (click again if you need to turn off).
  3. Click the Save button.

Field Properties

Field properties aid in the process of adding data to a table. For example, you may want to create a property that forces all names for a "Company" field to be in upper case. Properties are created in the design view of the table. You must click on a specific field name to view the properties for that field. These properties are viewed and edited in the bottom half of the window.

If you are unsure what a specific property can do for you, click in the window next to the property in question and press the F1 key. This will take you to the Help window for the specific property.

The following are some commonly used field properties for a Text data type.

Field size: by default, the size is 50. Limits how many characters can be typed into the table's field.

The > sign in the Format window will change the First Name values to upper case no matter how they are typed.

The Caption property allows your field heading in your table to illustrate differently than the actual name of the field.

Use the Default Value property when most of the values will be the same.

Input Mask Property

This property automates formatting for you. For example, telephone numbers can automatically have the parentheses for the area code and the dash in the phone number.

  1. Click on the desired field name (top half of design view window).
  2. Click in the Input Mask window (bottom half of design window). You should see this button.
  3. Click on the button to start the Input Mask Wizard.
  4. Click the Yes button to save the table (give the table a name if you have not done so yet and click OK).
  5. Select the desired input mask.
  6. Click the Finish button. Although there are other options in the Wizard, by clicking Finish (instead of Next) you are accepting all of the defaults in the wizard.

Creating Dropdown lists in your table.

Creating a dropdown list is a two-step process. First you will create a brand new table that consists of only one field listing the data you want in your drop down list. You will then attach this table to the field in your original table. The following is an example of how to create a drop down list for a preexisting database that contains a table with a field called "Building."

First you will create a brand new table...

  1. Create a new table in Design View
  2. Type in a name for the field for your drop down list. For example, call this field "Building" if you are making a drop down list of the buildings on campus.
  3. Press tab and use "Text" as the data type.
  4. Click the "Save" button. It will ask you if you want to create a Primary Key, click NO. Name this table: tblBuildingList
  5. Click the View button to go to the Table view.
  6. In the table view, you will only have one column. Type in the names of the buildings at Augustana.
  7. Close this table.
  8. Then you will attach this "tblBuildingList" to the field in your original table...
  9. Open the table you wish to create a drop down list in. Go to the Design view.
  10. Click on the Building field.
  11. Click on the tab below called "Lookup"
  12. Click on the drop down arrow of the Display Control and choose Combo Box
  13. Click in the window for "Row Source" and choose "BuildingList"
  14. Click the Save button
  15. Click on the View button to view the table. When you click in the column for "Building," you will get a drop down list. Type the first letter of the desired building and it will fill in the name for you. You may have to type the first two letters of the building if there are two or more buildings that start with the same letter.

You can use this same "BuildingList" in another file without having to recreate it. Open the file you wish to add it to and choose from the menu: File, Get External data, Import. Select the file you originally created the "BuildingList" in. It will pull up a window listing the tables in that file. Pick "BuildingList." Once the table is brought into this file, you will have to repeat steps 10-17.