Working with Multiple Tables in Access

Andrea Peach, Georgetown College

(most of the text in this document is from the help files for Microsoft Access. Comments added by me will be italicized)

In this session, we will:

create indexes and keys

create relationships between tables

create lookup fields (for drop down (combo) boxes)

run queries with more than one table

create a form based on more than one table (use the form wizard)

add a command button that will open a new form to an existing form

Database we will use for this session can be downloaded by clicking here

First, create all the tables you will need....

In the case of our Election Results data, you will create two tables. The first table, called ElectionResults, will contain the candidate (text 255), handcount (yes/no), gender (text 1), age (number), and comments (memo) (note: this has already been created for you in the sample database). The second table, called Candidates, needs to have the Candidate name and party. You will need to create this table. Make sure that the Candidate name is the same as the candidate field in the ElectionResults table (text 255).

Set or change the primary key

1. After we set up the tables, we will need to set the primary and foreign keys. The primary key of the Election Results table could be an autonumber field (which will automatically be generated by the system). To add this, click on the first field (candidate) and then right-click and choose Insert Rows. Then, on the new field that was created, give it a name (how about pkey) and choose Autonumber as the data type. Finally, click on the key tool on the toolbar to make it the primary key.
2. Let's also create a foreign key on the candidate field. This will aid us in creating our relationship! So, click on the candidate field and look in the gray box at the bottom of the screen. Choose the Indexed box and change it to yes (duplicates ok).
3. Finally, let's close the table and open the Candidates table. What should the primary key be (which field is unique). Well, the candidate field is unique (unless, of course there are two candidates with the same name and then we are in trouble!) So, make the candidate field the primary key.

Notes

• To select multiple fields, hold down the CTRL key and then click the row selector for each field.

• You can specify a primary key for a field that already contains data, but Microsoft Access generates a message when you save the table if it finds duplicate values or Null values in the field. If you encounter this message, you have three choices: use a Find Duplicates query to locate records with duplicate values or Null values and then edit the field to remove them; choose a different field; or add an AutoNumber field and set it as the primary key.
  
  
• In a multiple-field primary key, field order may be important to you. The fields in a multiple-field primary key are sorted according to their order in table Design view. If you want a different order, first specify the fields for the primary key as described in the preceding procedure, and then click Indexes  on the toolbar to display the Indexes window and reorder the field names for the index named PrimaryKey.

Define relationships between tables

1. Close any tables you have open. You can't create or modify relationships between open tables.
   
2. If you haven't already done so, switch to the Database window. You can press F11 to switch to the Database window from any other window.
   
3. Click Relationships  on the toolbar.
   
4. If your database doesn't have any relationships defined, the Show Table dialog box will automatically be displayed. If you need to add the tables you want to relate and the Show Table dialog box isn't displayed, click Show Table  on the toolbar. If the tables you want to relate are already displayed, skip to step 6.
   
5. Double-click the names of the tables you want to relate, and then close the Show Table dialog box. (note, in our case, you want to click on the candidates and the Election Results tables).
   
6. Drag the field that you want to relate from one table to the related field in the other table. (note, in our case, the candidates field from the Election Results to the candidates field in the Candidates table)

   To drag multiple fields, press the CTRL key and click each field before dragging them.

   In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key in the other table. The related fields don't have to have the same names, but they must have the same data type (with two exceptions) and contain the same kind of information. In addition, when the matching fields are Number fields, they must have the same FieldSize property setting. The two exceptions to matching data types are that you can match an AutoNumber field with a Number field whose FieldSize property is set to Long Integer; and you can match an AutoNumber field with a Number field if both fields have their FieldSize property set to Replication ID.

7. The Edit Relationships dialog box is displayed. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary.

   Set the relationship options if necessary. For information about a specific item in the Relationships dialog box, click the question mark button , and then click the item.

8. Click the Create button to create the relationship.
   
9. Repeat steps 5 through 8 for each pair of tables you want to relate.

   When you close the Relationships window, Microsoft Access asks if you want to save the layout. Whether you save the layout or not, the relationships you create are saved in the database.

Notes  

• If you need to view all the relationships defined in the database, click Show All Relationships on the toolbar. To view only the relationships defined for a particular table, click the table, and then click Show Direct Relationships on the toolbar.
  
  
• If you need to make a change to the design of a table, you can right-click the table you want to change, and then click Table Design.
  
  
• You can create relationships using queries as well as tables. However, referential integrity isn't enforced with queries.
  
  
• To create a relationship between a table and itself, add that table twice. This is useful in situations where you need to perform a lookup within the same table. For example, in the Employees table in the Northwind sample database, a relationship has been defined between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

Create a field that looks up data from another table in Design view

1. In Design view, open the table where you want to add the lookup field (in our case, the Election Results table).
   
2. Do one of the following:
   • If the field you want to use as the foreign key for the Lookup field already exists which ours does (candidates), click in that field's row.
3. In the Data Type column, click the arrow and select Lookup Wizard.
   
4. Click the option that indicates you want the Lookup field to look up the values in a table or query.
   
5. Click Next and follow the directions in the remaining Lookup Wizard dialog boxes.

   When you click the Finish button, Microsoft Access creates the Lookup field and sets certain field properties based on the choices you made in the wizard. Once you've created a Lookup list field, if you add the field to a form, Microsoft Access copies its definition into the form. The combo box or list box is created automatically for the form. However, if you change the definition of a Lookup or value list field in the table after adding it to a form, those changes will not be reflected in that form. To correct this, delete the field from the form and then add it again.

Note   It is also possible to add a Lookup field to a table that displays values from the same table that contains the Lookup field. For example, in the Employees table of the Northwind sample database the ReportsTo field is a Lookup field that displays data from the FirstName and LastName fields by looking up the corresponding EmployeeID in the same table.

Add a table or query to a query

1. Open a query in Design view.
   
2. On the toolbar, click Show Table .
   
3. In the Show Table dialog box, click the tab that lists the objects whose data you want to work with.
4. Click the name of the object you want to add to the query. To select additional objects one at a time, hold down CTRL while you click each object name. To select a block of objects, click the first name in the block, hold down SHIFT, and then click the last name in the block.
   
5. Click Add, and then click Close.

Notes  

• You can also add a table or query to a query by dragging the table or query name from the Database window to the upper part of query Design view.
  
  
• When you have multiple tables or queries in a query, they need to be joined together.

Ways to bring together data from multiple tables or queries in a query

The power of queries lies in being able to bring together or perform an action on data from more than one table or query. For example, you might want to view a customer's information with the orders the customer placed. To see this information, you need data from the Customers and Orders tables.

When you add more than one table or query to a query, you need to make sure their field lists are joined to each other with a join line so that Microsoft Access knows how to connect the information.

If tables in a query aren't joined to one another, either directly or indirectly, Microsoft Access doesn't know which records are associated with which, so it displays every combination of records (called a "cross-product" or "Cartesian product") between the two tables. Therefore, if each table had 10 records in it, the query's results will contain 100 records (10X10). It also means the query might take a long time to run and ultimately might produce less meaningful results.

If you previously created relationships between tables in the Relationships window, Microsoft Access automatically displays join lines when you add related tables in query Design view. If referential integrity is enforced, Microsoft Access also displays a "1" above the join line to show which table is on the "one" side of a one-to-many relationship and an infinity symbol  to show which table is on the "many" side.

Even if you haven't created relationships, Microsoft Access automatically creates joins if you add two tables to a query and the tables each have a field with the same or compatible data type and if one of the join fields is a primary key. The "one" and "many" symbols are not displayed in this case, because referential integrity is not enforced.

Sometimes the tables you add to the query don't include any fields that can be joined. In this situation, you have to add one or more extra tables or queries to serve solely as a bridge between the tables whose data you want to use. For example, if you add the Customers and Order Details tables to a query, they won't have a join line between them because they don't have any fields that can be joined. But the Orders table is related to both tables, so you can include the Orders table in your query to provide a connection between the other two.

Once tables and queries are joined, and you've added fields from both tables or queries to the design grid in query Design view, the default join tells the query to check for matching values in the join fields. (This is called an inner join in database terminology.) When it finds matches, it combines those two records and displays them as one record in the query's results. If one table or query doesn't have a matching record in the other table or query, neither record appears in the query's results. If you want the query to select all the records from one table or query whether or not it has matching records in the other table or query, you can change the join type.

Create a command button to open and synchronize, close, or print a form

1. Open a form in Design view.
   
   
2. Click the Control Wizards tool  in the toolbox if it's not already pressed in.
   
   
3. In the toolbox, click the Command Button tool .
   
   
4. On the form, click where you want to place the command button.
   
   
5. In the first wizard dialog box, click Form Operations in the Categories box, and then, in the Actions box, click the type of button that you want to create. (FOR EXAMPLE: You may want to open the form so you can enter a new student or (in the case of our example) a new candidate)
   
   
6. Follow the directions in the wizard dialog boxes. In the last dialog box, click Finish to display the command button in Design view.