c03ac07.qxd 12/14/2006 10:33 pm page 47 planning and...

Planning and Creating aCustom Database

Introduction

The Microsoft Office Access 2007 database wizards make cre-ating databases easy, but you may need to create a databasethat does not fit any of the predefined template choices. Inthat situation, you may need to create the database “fromscratch.”

Creating a database from scratch involves careful plan-ning. You must:

◆ Determine the purpose and scope of your database.

◆ Decide what tables your database will contain and what the content of those tables will be.

◆ Define how data in one table is related to data in another table.

When you create a database from scratch, you can takeadvantage of the tools that Access provides. If you don’t planto create a database from scratch but instead plan to use onlyexisting Access databases, you might not need the informa-tion in this chapter. Understanding database design concepts,however, will help you better understand how to create effec-tive queries later on. 3

3What You’ll Do

Create a Custom Database Template

Plan Tables

Create Tables in a Database

Create a Table by Entering Data

Create a Table Using a Template

Import Data into Tables

Link to Data in Tables

Working with Table Records

Work with a Table in Design View

Specify a Primary Key in Design View

Plan Table Relationships

Define Table Relationships

Create and Print a Table RelationshipReport

Ensure Referential Integrity

Identify Object Dependencies

Modify Object Dependencies

47

C03AC07.qxd 12/14/2006 10:33 PM Page 47

48

When you create a blank database in the Getting Started withMicrosoft Office Access 2007 dialog box, Access uses a database filecalled Blank.accdb for Access 2007 or Blank.mdb for earlier versions ofAccess located in the templates folder. If you want to create a customblank template, you can save your own database file with the nameBlank in the templates folder. The new custom blank template can con-tain any object type you want, such as forms, reports, macros, or addi-tional tables. If you no longer want to use a custom blank database,delete or rename the file named Blank, and Access creates a newdefault blank database for you.

Creating a CustomDatabase Template

Create a Custom Blank TemplateDatabase

Start Access, or click the Officebutton and then click New.

In the left pane, click Featuring.

Click Blank Database.

Click the Browse button.

Click the Save in list arrow, andthen select one of the followinglocations:

◆ System template folder.C:\Program Files\MicrosoftOffice\Templates\1033\Access.

A template in the System folderoverrides a template in theUser folder.

◆ User template folder.C:\Users\user name\Documents(Vista), or C:\Documents andSettings\user name\AppData\Microsoft\Templates (XP).

Click the Save as type list arrow,and then click a file type.

Type Blank.accdb (or Blank.mdb,if you are creating an earlier-version template) in the File namebox.

Click OK.

Click Create.9

8

7

6

5

4

3

2

1

1 32

9

4

5

7 8

6

C03AC07.qxd 12/14/2006 10:33 PM Page 48

Chapter 3 Planning and Creating a Custom Database 49

Create a Custom TemplateDatabase from an ExistingDatabase

Start Access, click the Officebutton and then click Open.

Click the Files of type list arrow,and then click a file type.

If the file is located in anotherfolder, click the Look in list arrow,and then navigate to the file.

Click the database file you want touse as a blank template.

Click Open.

Click the Office button, click SaveAs, and then select the format youwant: Access 2007 Database,Access 2002 - 2003 Database, orAccess 2000 Database.

Click the Save in list arrow, andthen select one of the followinglocations:

◆ System template folder.C:\Program Files\MicrosoftOffice\Templates\1033\Access.

◆ User template folder.C:\Users\user name\Documents(Vista), or C:\Documents andSettings\user name\AppData\Microsoft\Templates (XP).

Type Blank.accdb (or Blank.mdb,if you are creating an earlier-version template) in the File namebox.

Click Save.9

8

7

6

5

4

3

2

1

5

3

4

2

9

8

7

C03AC07.qxd 12/14/2006 10:34 PM Page 49

50

Although you can always make changes toyour database when necessary, a little plan-ning before you begin can save time later on.When you plan a database, consider how youwill use the data. What kind of data are youcollecting? What kind of data are you enter-ing? How are data values related to oneanother? Can your data be organized into sep-arate, smaller groups? What kinds of safe-guards can you create to ensure that errorsdo not creep into your data? As you considerthese questions, you should apply theanswers as you structure your database.

Plan TablesTables are one of the fundamental buildingblocks of a database. Database planningbegins with deciding how many and whatkinds of tables your database will contain.Consider organizing your database informa-tion into several tables—each one containingfields related to a specific topic—rather thanone large table containing fields for a largevariety of topics. For example, you could cre-ate a Customers table that contains only cus-tomer information and an Orders table thatcontains only order information. By focusingeach table on a single task, you greatly sim-plify the structure of those tables and makethem easier to modify later on.

Choose Data TypesWhen you create a table, you must decidewhat fields to include and the appropriate for-mat for those fields. Access allows you toassign a data type to a field, a format thatdefines the kind of data the field can accept.Access provides a wide variety of data types,ranging from text and number formats toobject-based formats for images, sound,

video clips, and embedded macros (New!).Choosing the correct data type helps youmanage your data and reduces the possibilityof data-entry errors. To make it easier to cre-ate fields, Access provides the Add New Fieldcolumn (New!) in Datasheet view so you canquickly enter a field name. If you already havea defined field in the database that you wantto use again, you can drag the existing fieldfrom the Field List pane (New!) on to thedatasheet and Access automatically creates arelationship or guides you through theprocess. You can also use field templates(New!) for creating new fields. A ffiieelldd tteemm--ppllaattee is a design for a field, complete with aname, data type, length, and predefined prop-erties. You can drag field templates onto thedatasheet. Field templates are XSD based sothat you can set up standard definitions forshared use. If you create a field for numbers,you can use the Totals row (New!) to calculatevalues using functions such as sum, count,average, maximum, minimum, standard devi-ation, or variance.

Specify a Primary KeyYou should also identify which field or fieldsare the table's primary keys. Primary keys arethose fields whose values uniquely identifyeach record in the table. A social securitynumber field in a personnel table could beused as a primary key, since each employeehas a unique social security number. A tablewith time-ordered data might have two pri-mary keys—a date field and a time field(hours and minutes), which together uniquelyidentify an exact moment in time. Althoughprimary keys are not required, using them isone way of removing the possibility of dupli-cate records existing within your tables.

Planning Tables AC07S-1.3.1,AC07S-1.3.2

C03AC07.qxd 12/14/2006 10:34 PM Page 50


After creating a database file, you need to cre-ate the tables that will store the data. Thereare several ways to create a new table: inDatasheet view, in Design view, with TableTemplates, SharePoint Lists, or by importing atable or linking to the data in a table fromanother Access database. Depending on themethod you choose, creating a table caninvolve one or more of the following:

◆ Specifying the fields for the table

◆ Determining the data type for each field

◆ Determining the field size (for text and number fields only)

◆ Assigning the primary key

◆ Saving and naming the table

Creating Tables in a Database

Methods for Creating a Table

Type Method Description

Datasheet Table button on When you create a table in Datasheet view, you can start the Create tab viewing and entering data right away. Access automatically

assigns a data type based on the kind of information you entered in the field, and it assigns a default field size for text and number fields. After you close and save the table, Access prompts you to identify a primary key or to allow Access to designate one for you.

Table Template Table Templates button When you create a table using table templates, you select the on the Create tab type of table you want. Access provides several table templates,

including Contacts, Tasks, Issues, Events, and Assets.

Design Design View button In Design view, you must specify the fields, specify the data type on the Create tab for each field, assign the size (for text and number fields), assign

the primary key, and save the table yourself.

SharePoint SharePoint Lists button When you create a table using data from a SharePoint list, you on the Create tab select the type of table you want. Access provides several table

types, including Contacts, Tasks, Issues, Events, Custom,and Existing SharePoint List.

Importing Access button on the If you want to use data from another Access database in the External Data tab database you are creating, you can import it. When you import a

table, all the field names and data types are retained with the imported data. However, you must name the new table and identify the primary key or have Access create a primary key for you. Also, you may need to change the field size and other properties after importing.

Linking Access button on the When you link a table, the data is retrieved from a table in External Data tab another database. Linking a table saves disk space because

there is only one table rather than multiple tables with the same data. Linking a table saves time because there is no need to update the same information in more than one table.

AC07S-1.2.2

C03AC07.qxd 12/14/2006 10:34 PM 1

52

Access allows you to display many of its objects in multiple viewingmodes. Datasheet view displays the data in your tables, queries, forms,and reports. Design view displays options for designing your Accessobjects. You can create a new table in both views. When you create atable in Datasheet view, you enter data and Access creates the tableas you type. Access determines the data type of each field based onthe data you enter (New!). When you finish entering data, Access willprompt you for the name of the table you’ve just created.

Creating a Table byEntering Data

Enter Data to Create a Table

Click the Create tab.

Click the Table button.

Enter the data.

Press Tab to move from field tofield or click in a cell.

To change a field name, double-click the field name, type the newname, and then press Enter.

Click the Save button on the QuickAccess Toolbar.

Type a table name.

Click OK.

To have Access set the primarykey, click Yes.

Click the Close button in the Tablewindow.

9

8

7

6

5

4

3

2

1

Did You Know?You can select or resize a column orrow like in Excel. To select a columnor row in a table, click the Column orRow selector. To resize a column orrow, drag the border between theColumn or Row selector. You can alsoclick the Home tab, and then click theMore button to access commands toresize columns and rows.

2 1 3

5 4

9

6

7

AC07S-2.2.1

C03AC07.qxd 12/14/2006 10:34 PM Page 52


A table template is a predefined table with fields you can quickly insertand use in a database. Access provides several table templates(New!) including: Contacts, Tasks, Issues, Events, and Assets. You canuse the Create tab to quickly insert a table template. After you insert atable template, you can change fields to meet your own needs, andthen name and save the table in the database.

Creating a Table Using a Template

Create a Table Using a Template

Click the Create tab.

Click the Table Templates button.

Click the table template (Contacts,Tasks, Issues, Events, or Assets)you want.

To change a field name, double-click the field name, type the newname, and then press Enter.


Type a table name.

Click OK.

To have Access set the primarykey, click Yes.

When you’re done, click the Closebutton in the Table window.

9

8

7

6

5

4

3

2

1

Did You Know?You can insert a subdatasheet in atable. In the Datasheet view of a table,click the Home tab, click the More but-ton, point to Subdatasheet, and thenclick Subdatasheet. Click the Tablestab, click the table, specify the foreignkey in the Link Child Fields box and theprimary key in the Link Master Fieldsbox, and then click OK.

You can view a subdatasheet. In theDatasheet view of a table, click theplus box next to the record to displaythe subdatasheet. Click the minus boxto hide the subdatasheet.

12

3

7

6

4

9

AC07S-2.2.3

C03AC07.qxd 12/14/2006 10:35 PM Page 53

54

You can create new tables from other Access databases by importingand linking tables. When you import a table, you copy data from a tablein one Access database and place it in a new table in your database.You can also import data from other programs. After you select thetable you want to import during the import process, you have the optionof saving the import steps for use again in the future.

Importing Data into Tables

Import a Table from a Database

Click the External Data tab.

Click the Import Access Databasebutton.

Click Browse, locate and selectthe database file that contains thedata you want to import, and thenclick Open.

Click the Import tables, queries,forms, reports, macros, andmodules into the current databaseoption.

Click OK.

Click the tables you want toimport. To deselect a table, clickthe table again.

Click OK.

To save import steps, select theSave import steps check box,enter a name and description, andthen click Save Import.

Otherwise, click Cancel.

8

7

6

5

4

3

2

1

3

5

4

6

7

Did You Know?You can select fields in the order youwant them to appear in the table. In the Sample Fields list, you canchoose the fields you want to includein your table in any order. The fieldsyou choose will appear in the table inthe order you chose them.

C03AC07.qxd 12/14/2006 10:35 PM Page 54


You can create new tables from other Access databases by importingand linking tables. When you link a table, the data stays in its originallocation, but you can display and access that data from within yourdatabase. If data in the original database changes, the changes willappear in your linked database, too. After you select the table you wantto link during the linking process, you have the option of saving theimport steps for use again in the future.

Linking to Data in Tables

Link to Data from a Database

Click the External Data tab.

Click the Import Access Databasebutton.

Click Browse, locate and selectthe database file that contains thedata you want to import, and thenclick Open.

Click the Link to the data sourceby creating a linked table option.

Click OK.

Click the tables you want toimport. To deselect a table, clickthe table again.

Click OK.

To save import steps, select theSave import steps check box,enter a name and description, andthen click Save Import.

Otherwise, click Cancel.

8

7

6

5

4

3

2

1

3

5

4

6

7

C03AC07.qxd 12/14/2006 10:35 PM Page 55

56

A database is made up of groups of fields organized into tables. A fieldis a specific category of information, such as a name or a product.Related fields are grouped in tables. You usually enter data into fieldsone entity at a time (one customer at a time, one product at a time, andso on). Access stores all the data for a single entity in a record. Youcan view a table in Datasheet or Design view. Design view allows youto work with your table’s fields. Datasheet view shows a grid of fieldsand records. The fields appear as columns and the records as rows.The first field in a table is often an AutoNumber field, which Accessuses to assign a unique number to each record. You can’t select orchange this value. You can use Record buttons at the bottom of thetable in Datasheet view to quickly move around and search for data ina table.

Working with Table Records

Enter a New Record and MoveAround in a Table

In the Navigation pane, double-click the table you want to open.

Click the New Record button.

Press Tab to accept theAutoNumber entry.

Enter the data. If you make atyping mistake, press Backspace.

Press Tab to move to the next fieldor Shift+Tab to move to theprevious field.

When you reach the end of therecord, click one of the Recordbuttons:

◆ First Record button.

◆ Previous Record button.

◆ Specific Record box. Enter arecord number in the box, andthen press Enter.

◆ Next Record button.

◆ Last Record button.

TIMESAVER You can also usebuttons in the Records group onthe Home tab to perform the samecommands.

6

5

4

3

2

1

2 46

AC07S-1.3.1, AC07S-3.1AC07S-3.2

C03AC07.qxd 12/14/2006 10:36 PM Page 56


Search for Records


If you want, click in the table fieldwhere you want to start thesearch.

Click in the Search box.

Type the text you want to find inthe table.

Press Enter to find the firstinstance of the text you want tofind.

Press Enter again to find the nextinstance of the text you want tofind.

When you’re done, delete the textin the Search box.

Delete a Record from a Table


Right-click the row selector withthe record you want to delete.

Click Delete Record, and thenclick Yes to confirm.

TIMESAVER Click the rowselector, click the Home tab, andthen click the Delete button.

3

2

1

7

6

5

4

3

2

1

2 3

1 2

3 4

C03AC07.qxd 12/14/2006 10:36 PM Page 57

58

Most Access objects are displayed in Design view, which allows you towork with the underlying structure of your tables, queries, forms, andreports. To create a new table in Design view, you define the fields thatwill comprise the table before you enter any data. In Design view fortables, each row corresponds to a field. You can edit, insert, and deletefields in your database tables in Design view. You insert a field byadding a row, while you delete a field by removing a row. You can alsochange field order by dragging a row selector to a new position.

Working with a Tablein Design View

Create or Modify a Table inDesign View

Click the Create tab, and then clickthe Table Design button, or selectthe table you want to modify in theNavigation pane, and then clickthe Design View button.

Click in a Field Name cell, andthen type a modified field name.

Click in a Data Type cell, click theData Type list arrow, and thenclick a data type.

Click in a Description cell, andthen type a description. If theProperty Update Options buttonappears, select an option, ifnecessary.

To insert a field, click the rowselector below where you wantthe field, and then click the InsertRows button on the Ribbon.

To delete a field, click the rowselector for the field you want todelete, and then click the DeleteRows button on the Ribbon.

Click the Save button on the QuickAccess Toolbar, and then ifnecessary, enter a table name andclick OK.

When you’re done, click the Closebutton in the Table window.

8

7

6

5

4

3

2

1

1

7 2 5 46

8

3

Propertyupdateoptionsbutton

AC07S-2.2.1

C03AC07.qxd 12/14/2006 10:37 PM Page 58

A primary key is one or more fields in a table that provide a uniqueidentifier for every row. You use the primary key in other tables to referback to the table with the primary key. In Design view, you can use thePrimary Key button to assign or remove the primary key designation forthe selected field or fields. When you create a new table in Datasheetview, Access automatically creates a primary key and assigns it thefield name “ID” and the data type AutoNumber. When you create atable in Design view, you can specify more than one field as a primarykey, known as a composite key, so you are responsible for determiningthe data type of the primary key. Whatever data type you choose, val-ues for the primary key must be unique for each table record.


Specifying a PrimaryKey in Design View

Specify a Primary Key

In Design view, create a field thatwill be that table’s primary key,and then select an appropriatedata type.

◆ If you choose the AutoNumberdata type, Access assigns avalue to the primary key for anew record that is one morethan the primary key in theprevious record.

◆ If you choose any other datatype, such as Text, Number, orDate/Time, during data entry,you must enter a unique valuein the appropriate format forthe primary key of each newrecord.

Click the row selector of that field.

◆ To create more than oneprimary key, press and holdCtrl, and then click theadditional row selector foreach field.

Click the Primary Key button.

To remove a primary key, deleteany table relationships associatedwith the primary key, select therow selector for the Primary key,and then click the Primary Keybutton.

4

3

2

13 2 1

AC07S-1.3.1

C03AC07.qxd 12/14/2006 10:37 PM Page 59

60

When you place data into separate tables, youneed some way of merging this data togetherfor forms and reports. You can do this byestablishing table relationships that indicatehow data in one table relates to data inanother.

Specifying Common FieldsData from several different tables is relatedthrough the use of common fields. A commonfield is a field existing in two or more tables,allowing you to match records from one tablewith records in the other tables. For example,the Customers table and the Orders tablemight both contain a Customer ID field, whichfunctions as a primary key that identifies aspecific customer. Using Customer ID as acommon field allows you to generate reportscontaining information on both the customerand the orders the customer made. When youuse a primary key as a common field, it iscalled a ffoorreeiiggnn key in the second table.

Building Table RelationshipsOnce you have a way of relating two tableswith a common field, your next task is toexpress the nature of that relationship. Thereare three types of relationships: one-to-one,one-to-many, and many-to-many.

A table containing customer names and asecond table containing customer addressesexist in a one-to-one relationship if each cus-tomer is limited to only one address.Similarly, a one-to-many relationship existsbetween the Customers table and the Orderstable because a single customer could placeseveral orders. In a one-to-many relationshiplike this, the “one” table is called the pprriimmaarryy

ttaabbllee, and the “many” table is called therreellaatteedd ttaabbllee.

Finally, if you allow several customers tobe recorded on a single order (as in the caseof group purchases), a many-to-many rela-tionship exists between the Customers andOrders tables.

Maintaining Referential IntegrityTable relationships must obey standards ofrreeffeerreennttiiaall iinntteeggrriittyy, a set of rules that controlhow you can delete or modify data betweenrelated tables. Referential integrity protectsyou from erroneously changing data in a pri-mary table required by a related table. Youcan apply referential integrity when:

◆ The common field is the primary table’s primary key.

◆ The related fields have the same format.

◆ Both tables belong to the same database.

Planning Table Relationships

Table Relationships

Choice Description

One-to-one Each record in one table is matched to only one record in a second table, and visa versa.

One-to-many Each record in one table is matched to one or more records in a second table, but each record in the second table is matched to only one record in the first table.

Many-to-many Each record in one table is matched to multiple records in a second table, and visa versa.

AC07S-1.2.1,AC07S-1.2.2

C03AC07.qxd 12/14/2006 10:37 PM Page 60


Referential integrity places some limitationson you.

◆ Before adding a record to a related table, a matching record must alreadyexist in the primary table.

◆ The value of the primary key in the primary table cannot be changed if matching records exist in a related table.

◆ A record in the primary table cannot be deleted if matching records exist ina related table.

Access can enforce these rules by cascad-ing any changes across the related tables. Forexample, Access can automatically copy anychanges to the common field across therelated tables. Similarly, if a record is deletedin the primary table, Access can automaticallydelete related records in all other tables.

As you work through these issues oftables, fields, and table relationships, you willcreate a structure for your database that willbe easier to manage and less prone to data-entry error.

Table relationship

Define table relationships

C03AC07.qxd 12/14/2006 10:38 PM Page 61

62

You can define table relationships in several ways. After you createtables in your database, you can define table relationships betweenthem. You can define and manage relationships using buttons on theDatabase Tools tab. This gives you control over your table relationshipsand also gives you a quick snapshot of all the relationships in yourdatabase. After you define a relationship, you can double-click the con-nection line to modify or add to the relationship.

Defining TableRelationships

Define Table Relationships

Click the Database Tools tab.

Click the Relationships button.

If relationships are alreadyestablished in your database, theyappear in the Relationshipswindow. In this window you cancreate additional tablerelationships.

If necessary, click the Show Tablebutton to display the Show Tabledialog box.

Click the Tables tab.

Click the table you want.

Click Add.

The table or query you selectedappears in the Relationshipswindow.

Repeat steps 5 and 6 for eachtable you want to use in arelationship.

Click Close.

Drag the common field in the firsttable to the common field in thesecond table. When you releasethe mouse button, a line appearsbetween the two tables, signifyingthat they are related. Also, the EditRelationships dialog box opens, inwhich you can confirm or modifythe relationship.

8

7

6

5

4

3

2

13

4

5

7

6

8

AC07S-1.2.1, AC07S-1.2.2AC07S-4.2.3

C03AC07.qxd 12/14/2006 10:38 PM Page 62


Click the Join Type button if youwant to specify the join type. ClickOK to return to the EditRelationships dialog box.

Click Create to create therelationship.

10

9

Did You Know?You can view the relationships youwant to see. Click the Design tabunder Relationship Tools, click theShow Direct Relationships button tosee tables that are directly related toeach other. Click the All Relationshipsbutton to see all the relationshipsbetween all the tables and queries inyour database.

You can print the Relationships window. Click the Design tab underRelationship Tools, click theRelationships Report button theRelationships window you want toprint, click the Print button, select theprint settings you want, and then click OK.

You can delete a table relationship. In the Relationships window, right-click the line that joins the tables thatyou no longer want related to oneanother, and then click Delete. In themessage box, click Yes to confirm thatyou want to permanently delete thisrelationship. You will not be able toundo this change.

Join Types

Join Types Description

Include rows only where Choose this option if you want to seethe joined fields from one record in the second table forboth tables are equal every record that appears in the first

table. The number of records you see in the two tables will be the same.

Include ALL records from Choose this option if you want to see“xxx” (the first table) all the records in the first table (evenand only those records if there is no corresponding record infrom “yyy” (the second the second table) as well as the recordstable) where the joined from the second table in which thefields are equal joined fields are the same in both

tables. The number of records you seein the first table might be greater thanthe number of records in the second table.

Include ALL records from Choose this option if you want to see all“yyy” (the second table) the records in the second table (even ifand only those records there is no corresponding record in thefrom the “xxx” (the first first table) as well as the records fromtable) where the joined the first table in which the joinedfields are equal fields are the same in both tables. The

number of records you see in the second table might be greater than thenumber of records in the first table.

10

9

C03AC07.qxd 12/14/2006 10:38 PM Page 63

64

After you create a table relationship, you can create and print a report.In the Relationships window, the Relationship Report button creates areport with the currently displayed relationship and switches to PrintPreview, where you can print the report. When you close Print Preview,the report appears in Design view. If you want, you can make changesto the report. To complete the process, you need to save and name thereport or close it without saving it. Instead of printing the report, youcan also use exporting tools in Print Preview to save the report inanother format, including a PDF or XPS document.

Creating and Printing a Table RelationshipReport

Create and Print a TableRelationship Report



If relationships are alreadyestablished in your database, theyappear in the Relationshipswindow.

Do any of the following to displaythe table relationships you want touse to create a report:

◆ Hide tables. Click the table youwant to hide, and then click theHide Table button.

◆ Add tables. Click the Tablestab, click the table you want,click Add, and then click Close.

Click the Relationship Reportbutton.

The report appears in PrintPreview.

To print the report, click the Printbutton, specify the settings youwant, and then click OK.

Click the Close Print Previewbutton.

The relationship report appears inDesign view.

6

5

4

3

2

1

4 3

5 6Relationship report

AC07S-1.2.3

C03AC07.qxd 12/14/2006 10:39 PM Page 64



Type a name for the relationshipreport.

Click OK.9

8

7

Did You Know?You can show direct relationships. Inthe Relationships window, click theDirect Relationships button to displayonly tables that relate to each other.

You can clear relationships. In theRelationships window, click the ClearLayout button, and then click Yes toremove the layout of the Relationshipswindow.

Specify the printoptions you want

7

9

8

See AlsoSee “Creating a PDF Document” onpage 300 or “Creating an XPSDocument” on page 301 for informationon using and saving a file with differ-ent formats.

Did You Know?You can show direct relationships. Inthe Relationships window, click theDirect Relationships button to displayonly tables that relate to each other.

C03AC07.qxd 12/14/2006 10:39 PM Page 65

66

Referential integrity in table relationships keeps users from accidentallydeleting or changing related data. If a primary table contains a list ofemployees and related tables contain additional information aboutthose employees, and an employee quits, his record is removed fromthe primary table. His records should also be removed in all relatedtables. Access allows you to change or delete related data, but only ifthese changes are cascaded through the series of related tables. Youcan do this by selecting the Cascade Update Related Fields andCascade Delete Related Records check boxes in the Edit Relationshipsdialog box.

Ensuring ReferentialIntegrity

Ensure Referential Integrity



Click the join line for therelationship you want to work with.

Click the Edit Relationships button.

Select the Enforce ReferentialIntegrity check box to ensure thatreferential integrity always existsbetween related tables in thedatabase.

If you want changes to the primaryfield of the primary tableautomatically copied to the relatedfield of the related table, select theCascade Update Related Fieldscheck box.

If you want Access to deleterecords in the related tableswhenever records in the primarytable are deleted, select theCascade Delete Related Recordscheck box.

Click OK.8

7

6

5

4

3

2

14

3 7

8

6

5

AC07S-1.2.2

C03AC07.qxd 12/14/2006 10:40 PM Page 66

As you develop a database, you create a relationship between objectsto share data and provide the information in forms and reports. Whenyou make changes to one object, it might affect another object. Forexample, if you no longer need a field in a table, instead of deleting itright away and possibly creating problems, you can check objectdependencies to make sure that the field you want to delete is not usedin another table. Checking for object dependencies helps you save timeand avoid mistakes. Access generates dependency information bysearching name maps maintained by the Name AutoCorrect feature. IfTrack Name AutoCorrect Info is turned off on the Current Databasepane in the Options dialog box, you cannot view dependency information.


Identifying ObjectDependencies

View Dependency Information

In the Navigation pane, click thedatabase object in which you wantto view dependencies.


Click the Object Dependenciesbutton.

Click the Objects that depend onme or Objects that I depend onoption.

The Object Dependencies taskpane shows the list of objects thatuse the selected object.

To view dependency informationfor an object listed in the pane,click the Expand icon (+) next to it.

When you’re done, click the Closebutton on the task pane.

6

5

4

3

2

1

Did You Know?You can view a list of databaseobjects. Click the Office button, clickpoint to Manage, click DatabaseProperties, and then click the Contentstab. When you’re done, click OK.

Dependency Information

Object Dependent Description

Table Table or query A relationship is defined between the objects

Table Form Fields in the selected table look up values

Query Table or query Query is bound to the table or query

Form Table or query Form is bound to the table or query

Form Form Form includes the other form as a subform

Report Table or query Report is bound to the table or query

Report Form Report includes the form as a subform

Report Report Report include the other report as a subreport

1 2 3 4

6

5

AC07S-6.2.4.1

C03AC07.qxd 12/14/2006 10:40 PM Page 67

As you view dependencies for an object in the Object Dependenciestask pane and determine you can make changes without effectingother objects, you can open an object in Design view directly from theObject Dependencies task pane and modify it. If you need to look atother objects, you can open more than one from the ObjectDependencies task pane. If you are having problems viewing depend-ency information, you need to turn on the Track Name AutoCorrect Infooption on the Current Database pane in Access Options.

68

Modifying ObjectDependencies

Modify Dependency Information

In the Navigation pane, click thedatabase object in which you wantto view and modify dependencies.


Click the Object Dependenciesbutton.

Click the Objects that depend onme or Objects that I depend onoption.

The Object Dependencies taskpane shows the list of objects thatuse the selected object.

Click the Expand icon (+) next to an object to view dependencyinformation.

Click the object you want to openin Design view.

Modify the object in Design view,and then click the Close button inthe Design view window.

When you’re done, click the Closebutton on the task pane.

8

7

6

5

4

3

2

11 2 3 47

8

6

5

AC07S-6.2.4.1

C03AC07.qxd 12/14/2006 10:40 PM Page 68