Locked lesson.
About this lesson
Learn how to lay out the Relationships Diagram to see what you have in the database.
Exercise files
Download this lesson’s related exercise files.
Services_18_Start_Relationships.accdb1.6 MB Services_18_Relationships.accdb
1.6 MB
Quick reference
Relationships and Relationships Diagram
Application Terminology
Add Tables to the Relationships Diagram
It is often quicker to drag tables from the Navigation Pane to the Relationships Diagram. You can drag them one-by-one, or select multiple tables and drag the selection.
You can also click the Show Table button on the RELATIONSHIP TOOLS, DESIGN ribbon to open the Show Table dialog box. Double-click to add tables, or select them and click Add. You can use the same keys to select multiple tables as in the Navigation Pane.
All Relationships
To see all relationships that are set up in the database, choose All Relationships from the RELATIONSHIP TOOLS, DESIGN ribbon.
Cascade Delete Related Records
Cascade Delete Related Records means that if a record is deleted in the main table and it has related records, they will be deleted at the same time.
As a general rule, do not check Cascade Delete Related Records. There are times, however, you may want to temporarily check this to clear out data quickly. Don’t forget to set it back as this can delete information that you really want to keep.
Cascade Update Related Fields
If changes are made to the value of the primary key in the main table, the same changes will automatically be made to related tables. When using AutoNumber for the Primary Key in the main table, this is usually not applicable.
Clear Layout
To clear the layout of the Relationships Diagram, click the Clear Layout icon on the RELATIONSHIP TOOLS, DESIGN ribbon. Clearing the layout does not delete relationships, it only clears the diagram.
Create Relationship
When creating relationships, the data types must match. This means Field Size too. By default, AutoNumbers are Long Integers.
Drag from a Primary Key to a Foreign Key. When you let go of the mouse, the Edit Relationships dialog box appears. Look at the field names listed to ensure they are correct. Check the Enforce Referential Integrity box.
While Access does not require you to drag from a Primary Key, that is the field you should use. At least use a field with a Unique index – otherwise, the relationship will not be a good one.
One reason to qualify ID for fieldnames (i.e., ServiceID, CustomerID, ImvoiceID) is so that you don't accidentally create relationships from one AutoNumber to another … I have seen this many times! Obviously, this is not right.
When a piece of data is the same, my preference is to keep the Primary and Foreign Key fieldnames the same. This alleviates confusion down the road.
Default Value
When creating key fields, make sure to delete the Default Value of 0 (zero) that Access automatically assigns for numeric data types … unless this is a value that matches up and is really what you want.
Delete Relationship
To delete a relationship, right-click on the link line and choose Delete from the shortcut menu.
You can also click a relationship to select it and press the DELETE key on your keyboard. I prefer the first method though, as it is safer. If you accidentally delete a table, you can drag it back on again.
Direct Relationships
If there are relationships already set up, it can be more convenient to add a table to the diagram and choose Direct Relationships from the RELATIONSHIP TOOLS, DESIGN ribbon. You can also right-click on a table on the Relationships Diagram and choose Show Direct from the shortcut menu.
Document the Relationships Diagram
To document the Relationships Diagram, press the Print Screen key to put an image of the screen on the Windows Clipboard. You can then paste the screen into another application such as Word or PowerPoint to print it out.
If you have a screen capture program such as Snagit (TechSmith), you can select just a rectangle of the screen. I like to then color the background white so when it is printed, I can add my own notes.
You can also choose Relationship Report on the RELATIONSHIP TOOLS, DESIGN ribbon. However, I prefer to put diagrams in PowerPoint or Word (use Landscape) for annotating and labeling.
Edit Relationship
To edit a relationship, right-click on it and choose Edit Relationship from the shortcut menu. You can also double-click a relationship to edit.
Enforce Referential Integrity
Enforce Referential Integrity is a checkbox in the Edit Relationships dialog box.
By enforcing Referential Integrity, you are telling Access that it is not okay to delete a record in the main table if there are related records. Also, values in a related table will not be allowed if they do not match any value in the main table.
Foreign Key
A Foreign Key is a field (or combination of fields) in a table that relates to a primary key in another table. Foreign Keys are often single fields using a Long Integer data type that relate to an AutoNumber field in the main table.
Maximize Window
A quick way to maximize a window is to double-click the Title Bar. The Title Bar is a much bigger target than clicking on the maximize button in the upper left, so faster!
Minimize Relationship Lines Crossing
To minimize relationship lines crossing, rearrange fields in the tables. Order does not matter. I like to put key fields on the top and tracking fields on the bottom.
Primary Key
A Primary Key is a field (or combination of fields) in a table that has a unique value for every record and is designated with a key icon in the row selector box.
You will also see an index in the Indexes window that has Primary and Unique properties set to Yes. The Index name does not matter, but is usually called Primary Key.
Referential Integrity
Referential Integrity is a property of a relationship that ensures there will be no foreign key values that do not exist in the main table (which should be the Primary Key). Unless you have a specific reason not to check Enforce Referential Integrity, then you should always choose it.
Relationships Diagram
As you build your database and define your key fields, lay out the Relationships Diagram. This is a good way to see what you have and a great help while you are building to see the field names.
To get a better view of what is in the database, stretch fieldlists to show all the fields. If you do not have long names, also make the fieldlists narrower. If you do have long names, you may have to make them wider. It is good to show all the names.
Arrange tables from left to right, as data must be entered.
Select Multiple Tables
To select multiple tables, click on the first table then shift-click on the last table. To toggle the selection status, ctrl-click.
Table Design
For a quick way to the table design, right-click a table on the Relationships Diagram and choose Table Design from the shortcut menu. When you save changes to a table design, the Relationships Diagram will change too.
If you want to change the data type of a field that is part of a relationship, you must first delete the relationship. You can, however, change field names and other properties without deleting relationships.
Table Indexes
To toggle the display of the table Indexes window, click the lightning bolt icon on the TABLE TOOLS, DESIGN ribbon
This window shows two indexes:
- Primary Key on ServiceID
- Multi-field Unique index on the combination of ServTyID (service type) and ServName (service name). This ensures that the same service will not be duplicated for a service type.
Unique Index
A Unique index can be set on a field that is not the Primary Key but must also be unique. A good example is when defining types and you do not want the text value of a type to be duplicated. When creating indexes, they should be on short fields – so set the Field Size accordingly.
Steps
Show Relationships Diagram
- To show the Relationships Diagram, click the Relationships icon on the DATABASE TOOLS ribbon tab
Add Tables to Relationships Diagram
- To add tables to Relationships Diagram, drag them from the Navigation Pane
- or click the Show Table command on the DESIGN ribbon tab of RELATIONSHIP TOOLS
Create Relationship
- To create a relationship, drag from a Primary Key to a Foreign Key. When you let go of the mouse, the Edit Relationships dialog box appears.
- Look at the field names listed to ensure they are correct.
- Check the Enforce Referential Integrity box.
When creating relationships, the data types must match. This means Field Size too. By default, AutoNumbers are Long Integers.
Document Relationships Diagram using Access
- To get a quick report of the Relationships Diagram from Access, click the Relationship Report on the RELATIONSHIP TOOLS, DESIGN ribbon
This report, however, is not as nice as the diagram itself. Better to document using a screen shot.
Document the Relationships Diagram using a Screen Shot
- To document the Relationships Diagram, press the Print Screen key to put an image of the screen on the Windows Clipboard.
- You can then paste the screen into another application such as Word or PowerPoint to modify it, annotate it, and print it out.
Edit Relationship
- To edit a relationship, right-click on the relationship line
- Choose Edit Relationship from the shortcut menu
- Alternately, double-click a relationship line to edit.
- 00:04 As you build your database,
- 00:06 lay out your Relationships Diagram.
- 00:08 So that you can get practice creating relationships,
- 00:11 I have deleted most of the relationships
- 00:14 in this database for your start lesson.
- 00:17 Hi, this is Crystal.
- 00:19 To define the relationships, click on the
- 00:22 Database Tools ribbon and choose Relationships.
- 00:25 If you are not maximized, double-click the titlebar
- 00:29 to maximize the relationships window within Access.
- 00:33 In the Navigation Pane, click on the first table,
- 00:36 then shift-click on the last table to select them all.
- 00:39 Let go of the mouse and drag the selected tables
- 00:42 to the Relationships Diagram
- 00:45 To give yourself more room to focus on relationships,
- 00:49 collapse the Navigation Pane
- 00:51 by clicking the close shutter icon.
- 00:53 Also double click on one of the ribbon tabs
- 00:56 to collapse the ribbon.
- 00:58 I am dragging the import tables out of the way.
- 01:01 They are here because the data still needs to be incorporated.
- 01:05 I am also moving the MyDataTypes table out of the way
- 01:08 since this is a test table for learning about data types
- 01:12 and has nothing to do with Joe's service information.
- 01:16 I like to show all tables on the Relationships Diagram
- 01:19 since this provides a great birds-eye view
- 01:22 of what is in a database.
- 01:25 Move the other tables so you can see everything.
- 01:28 Services and Customers are
- 01:30 the reason this database got created.
- 01:32 Each of these tables has Foreign Keys
- 01:35 so leave a little room to the left of them.
- 01:38 As a general rule, arrange tables on the diagram
- 01:41 from left to right so the data flows as it must be entered.
- 01:46 MyCompany is for customizing this application
- 01:49 to use for your business.
- 01:51 MyCompany currently just has one record.
- 01:54 The Primary Key is WhoID, which is an AutoNumber field.
- 01:59 As you can see, MyCompany is already
- 02:01 related to two other tables using WhoID.
- 02:05 Even though the Relationships Diagram was blank
- 02:08 when we started, that is only because the layout was cleared.
- 02:12 Deleting tables with relationships from the diagram
- 02:15 does not delete relationships.
- 02:18 To delete a relationship, right-click on the link line
- 02:21 and choose Delete from the shortcut menu.
- 02:24 When creating relationships, the data types must match.
- 02:28 In MyCompany, WhoID is an AutoNumber,
- 02:32 which is a special form of Long Integer.
- 02:35 In related tables, WhoID must also be a Long Integer.
- 02:39 I move the Work Services table farther to the right
- 02:42 since it has several Foreign Keys.
- 02:45 I name fields with ID to indicate a key field.
- 02:49 I put the Customers table on top
- 02:51 and stretch the fieldlists to show all the fields.
- 02:55 Service Types are categories of services offered.
- 02:59 Services are related to service types using ServTyID.
- 03:04 The ServiceTypes table needs to be
- 03:06 to the left of the Services table
- 03:08 since, in order to specify a service type in Services,
- 03:12 the record needs to first exist in service types.
- 03:16 Services are the tasks for each service type
- 03:20 and define the rate.
- 03:22 Drag a relationship from the Primary Key
- 03:24 in the service types table, ServTyID,
- 03:28 to the Foreign Key in the related table.
- 03:31 Always drag from a Primary Key to a Foreign Key.
- 03:35 After you drag a relationship and let go,
- 03:38 the Edit Relationships dialog box appears.
- 03:42 Check the Enforce Referential Integrity box
- 03:45 to ensure there can't be Foreign Key values that don't have
- 03:49 a matching Primary Key in the main table.
- 03:53 Time Types define how work will be charged.
- 03:56 Time Types does not have any Foreign Keys,
- 03:58 so position it all the way to the left.
- 04:01 As you move tables on the diagram,
- 04:03 resize them to show everything.
- 04:06 Work is what you do for a customer
- 04:08 so there is a Foreign Key called CustID.
- 04:11 CustID is the Primary Key in the Customers table.
- 04:15 Drag a relationship from CustID in the Customers table
- 04:19 to CustID in the Work table,
- 04:21 and Enforce Referential Integrity.
- 04:24 Work is done on a certain date, or a date range.
- 04:28 The WorkServices table specifies the tasks done
- 04:32 Each task may be calculated differently,
- 04:35 depending on how it is charged.
- 04:37 Drag a relationship from the TimeTypes table to WorkServices
- 04:41 on TimeTyID and Enforce Referential Integrity.
- 04:46 The Services table has rates that depend on the time type.
- 04:50 Work Services has a Foreign Key called WorkID
- 04:54 that relates to the WorkID Primary Key in the Work table.
- 04:58 Each WorkService also has a ServiceID to specify each task.
- 05:04 Drag a relationship from ServiceID in the Services table
- 05:07 to ServiceID in the WorkServices table
- 05:10 and Enforce Referential Integrity.
- 05:14 In the WorkServices table, I have placed
- 05:16 WorkID and ServiceID above the Primary Key to indicate
- 05:20 that the combination of Work and Service is unique.
- 05:24 To minimize the join lines crossing on the diagram,
- 05:28 I will move the InvoiceID up in the order.
- 05:30 Right-click on a table on the Relationships Diagram
- 05:34 and choose Table Design.
- 05:36 Order of fields does not matter.
- 05:38 I like to put key fields at the top
- 05:40 and tracking fields on the bottom.
- 05:43 WorkServices specifies what was done on a job
- 05:46 and also provides the detail for invoices.
- 05:49 This is why it is positioned to the left
- 05:52 instead of the right of the Invoices table.
- 05:55 In this case, an invoice will be created
- 05:58 from the detail for each company (WhoID),
- 06:00 instead of first creating an invoice
- 06:02 and then entering detail records.
- 06:05 Because InvoiceID is in the WorkServices table,
- 06:08 it can be deleted from the Work table.
- 06:11 Go to the table design of Work,
- 06:13 click in the box to the left of InvoiceID, and
- 06:16 press the DELETE key.
- 06:18 Close the Work table and save the changes.
- 06:21 The invoices table also has a reference to the Customer.
- 06:25 This information could actually be traced back
- 06:27 but is included in the Invoices table for convenience.
- 06:31 Drag a relationship from CustID in the Customers table
- 06:35 to CustID in the WorkServices table
- 06:38 and Enforce Referential Integrity.
- 06:41 All the tables have the same 4 tracking fields,
- 06:44 so it is not necessary to show them in every table.
- 06:49 IDadd is the user who added the record.
- 06:52 IDedit is the user who edited the record.
- 06:55 dtmAdd is the date/time a record was created.
- 06:59 dtmEdit is the date/time a record was modified.
- 07:03 Rearrange the tables, and fields,
- 07:06 to mimimize join lines crossing.
- 07:08 You will have to play around with this a bit
- 07:10 to get the clearest diagram that you can.
- 07:13 Data Structure and Relationships are very important.
- 07:17 Spend time up front to save time down the road.
- 07:21 In the next lesson, we will create forms.
Lesson notes are only available for subscribers.