Manage relationships by using the keyboard

You can use the keyboard to create and customize the relationships between your tables in a database. You can also create relationships for queries as well as tables, although referential integrity isn't enforced with queries.

Note  In the steps below, you can use the TAB key to move between selections in a dialog box, unless otherwise specified.

Define relationships by using the keyboard

1. Close any tables that 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. Press ALT+T to select the Tools menu, and then press R to open the Relationships window.
4. If your database doesn't have any relationships defined, the Show Table dialog box appears automatically. If you need to add the tables that you want to relate and the Show Table dialog box does not appear, press ALT+R to select the Relationships menu, and then press T to open the Show Table dialog box. If the tables that you want to relate already appear in the Relationships window, skip to step 7.
5. In the Show Table dialog box, select the first table that you want to relate, and then press ENTER to add it to the Relationships window.
6. Repeat step 5 for any other tables you want to relate. Use the DOWN ARROW key to select the table, and then press ALT+C to close the Show Table dialog box.
7. Press ALT+R to select the Relationships menu, and then press R to open the Edit Relationships dialog box.
8. Press ALT+N to open the Create New dialog box.
9. In the Left Table Name box, use the DOWN ARROW key to select the name of the table that contains the primary key.
10. In the Right Table Name box, select the name of the table that contains the foreign key.
11. In the Left Column Name box, select the primary key field, and in the Right Column Name box, select the foreign key field.

    Notes

    • The related fields don't have to have the same names, but they must have the same data type and contain the same kind of information.

    • When the matching fields are Number fields, they must have the same FieldSize property setting.

    • There are two exceptions to matching data types: 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 the FieldSize property for both fields is set to Replication ID.

12. Press ENTER.
13. In the Edit Relationships dialog box, use the arrow keys to make sure that the two columns contain the field names you want; you can change them if necessary, or you can specify additional fields if you are using a multiple-field primary key.

    Set relationship options if necessary. For information about a specific item in the Relationships dialog box, select the item and then press F1.

14. Press ALT+C to create the relationship.
15. Repeat steps 5 through 14 for each pair of tables you want to relate.

Notes

• If you need to view all the relationships that are defined in the database, press ALT+R to select the Relationships menu, and then press L. To view only the relationships defined for a particular table, select that table, press ALT+R to select the Relationships menu, and then press C.
• If you need to make a change to the design of a table, first select that table. Next, press SHIFT+F10 to select the shortcut menu, and then select Table Design.
• 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 an Employees table, you might define a relationship between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

Edit or delete a relationship by using the keyboard

1. Close any tables that you have open. You can't modify or delete 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. Press ALT+T to select the Tools menu, and then press R to open the Relationships window.
4. Press ALT+R to select the Relationships menu, and then press R to open the Edit Relationships dialog box.
5. Press ALT+T. In the Table/Query box, press the F4 key, and use the DOWN ARROW key to select the name of the table that contains the primary key, and then make sure that the appropriate table appears in the Related Table/Query box.
6. Set the relationship options if necessary. For more information about a specific item in the Relationships dialog box, select the item, and then press F1.
7. If you want to delete the relationship, press ENTER, and then press DELETE.

Define the default join type in a relationship by using the keyboard

1. Press F11 to switch to the Database window from any other window.
2. Press ALT+T to select the Tools menu, and then press R to open the Relationships window.
3. Press ALT+R to select the Relationships menu, and then press R to open the Edit Relationships dialog box.
4. Press ALT+T. Press F4 to open the Table/Query box, select the name of the table that contains the primary key, and then select the appropriate table in the Related Table/Query box.
5. Press ALT+J. In the Join Properties dialog box, select the desired join type.

   Option 1 defines an inner join. This is the default.

   Option 2 defines a left outer join.

   Option 3 defines a right outer join.

Notes

• You cannot change the default join type if the tables are linked tables. However, if the linked tables are stored in another Microsoft Access database, you can open the database in which they are stored to set the join type.

• Defining the join type for a relationship in the Relationships window doesn't affect the relationship itself; instead it sets the kind of join that will be used by default when you are creating queries based on the related tables. You can override the default join type later when you are defining a query.