Download
1 / 34

Access 2010 Level 1 Unit 1 Creating Tables and Queries - PowerPoint PPT Presentation


  • 116 Views
  • Uploaded on

Access 2010 Level 1 Unit 1 Creating Tables and Queries Chapter 2 Creating Relationships between Tables. Creating Relationships between Tables. Quick Links to Presentation Contents. Create Related Tables CHECKPOINT 1 Create a One-to-One Relationship

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Access 2010 Level 1 Unit 1 Creating Tables and Queries' - missy


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Access 2010

Level 1

Unit 1 Creating Tables and Queries

Chapter 2Creating Relationships between Tables


Creating relationships between tables
Creating Relationships between Tables

Quick Links to Presentation Contents

  • Create Related Tables

  • CHECKPOINT 1

  • Create a One-to-One Relationship

  • Display Related Records in a Subdatasheet

  • CHECKPOINT 2


Create related tables
Create Related Tables

  • Generally, a database management system fits into one of two categories—either a file management system (also sometimes referred to as a flat file database) or a relational database management system.


Create related tables continued
Create Related Tables…continued

  • Taking time to plan a database is extremely important.

  • Creating a database with related tables takes even more consideration.

  • You need to determine how to break down the required data and what tables to create to eliminate redundancies.


Create related tables continued1
Create Related Tables…continued

  • Along with deciding on the necessary tables for a database, you also need to determine the relationship between tables.

  • The ability to relate, or “join,” tables is what makes Access a relational database system.

database diagram


Create related tables continued2
Create Related Tables…continued

  • When a new record is added to a table, Access checks to ensure that there is no existing record with the same data in the primary key. If there is, Access displays an error message indicating there are duplicate values and will not allow the new record to be saved.

  • When adding a new record to a table, the primary key field cannot be left blank. Access expects a value in each record in the table and this is referred to as entity integrity.


Create related tables continued3
Create Related Tables…continued

To display a table in Design view:

  • Open the desired table.

  • Click the View button in the Views group in the Home tab.

View button


Create related tables continued4
Create Related Tables…continued

To add or remove a primary key from a field:

  • Click the desired field in the Field Name column.

  • Click the Table Tools Design tab.

  • Click the Primary Key button in the Tools group.

Primary Key button


Create related tables continued5
Create Related Tables…continued

  • Typically, a primary key field in one table becomes the foreign key field in a related table.

primary key and associated foreign key


Create related tables continued6
Create Related Tables…continued

  • In Access, one table can be related to another, which is generally referred to as performing a join. For example, in Project 1b, you will create a relationship between the Suppliers table and the Products table. The relationship you establish will ensure that a supplier number cannot be entered in the Products table without first being entered in the Suppliers table.

  • This type of relationship is called a one-to-many relationship, which means that one record in the Suppliers table will match zero, one, or many records in the Products table.


Create related tables continued7
Create Related Tables…continued

To create a one-to-many relationship:

  • Open the desired database.

  • Click the Database Tools tab.

  • Click the Relationships button in the Relationships group.

    continues on next slide…

Relationships button


Create related tables continued8
Create Related Tables…continued

  • At the Show Table dialog box, click the first table name.

  • Click the Add button.

  • Repeat Steps 4 and 5 until all desired table names have been added.

  • Click the Close button.

    continues on next slide…

Show Table dialog box


Create related tables continued9
Create Related Tables…continued

  • At the Relationships window, drag the common field to the related table.

    continues on next slide…

Relationships window


Create related tables continued10
Create Related Tables…continued

  • At the Edit Relationships dialog box, check to make sure the correct field name displays and the relationship type displays as One-To-Many.

  • Specify the relationship options.

  • Click the Create button.

Edit Relationships dialog box


Create related tables continued11
Create Related Tables…continued

  • Choose Enforce Referential Integrity at the Edit Relationships dialog box to ensure that the relationships between records in related tables are valid.

  • Referential integrity can be set if the field from the primary table is a primary key and the related fields have the same data type.


Create related tables continued12
Create Related Tables…continued

  • In the image below, the Suppliers table displays with a black line attached along with the number 1 (signifying the “one” side of the relationship). The black line is connected to the Products table along with the infinity symbol (signifying the “many” side of the relationship).

join line


Create related tables continued13
Create Related Tables…continued

To print relationships:

  • Click the Database Tools tab.

  • Click the Relationships button.

  • Click the Relationship Report button in the Tools group.

    continues on next slide…

Relationship Report button


Create related tables continued14
Create Related Tables…continued

  • Click the Print button.

  • Click OK.

  • Click the Close button.

Print button


Create related tables continued15
Create Related Tables…continued

To display the Show Table dialog box:

  • Click the Show Table button in the Relationships group.

Show Table button


Create related tables continued16
Create Related Tables…continued

To edit a relationship:

  • Click the Database Tools tab.

  • Click the Relationships button in the Relationships group.

  • Click the Edit Relationships button in the Tools group.

  • Make the desired changes at the Edit Relationships dialog box.

  • Click OK.

Edit Relationships dialog box


Create related tables continued17
Create Related Tables…continued

To delete a relationship:

  • Click the Database Tools tab.

  • Click the Relationships button in the Relationships group.

  • Right-click on the black line connecting the related tables.

  • Click the Delete option.

  • Click Yes.

Delete option


Checkpoint 1
CHECKPOINT 1

  • Access is this type of database management system.

    • flat file

    • relational

    • directional

    • organizational

  • The infinity symbol signifies this side of the relationship.

    • many

    • one

    • two

    • three

Answer

Answer

Next Question

Next Question

  • Typically, a primary key field in one table becomes this key field in a related table.

    • figure

    • international

    • foreign

    • secondary

  • To edit a relationship, click the Relationships button in this tab.

    • Home

    • Database Design

    • Database Tools

    • Create

Answer

Answer

Next Question

Next Slide


Create a one to one relationship
Create a One-to-One Relationship

  • You can create a one-to-one relationship between tables in which each record in the first table matches only one record in the second table and one record in the second table matches only one record in the first table.

one-to-one relationship


Create a one to one relationship continued
Create a One-to-One Relationship…continued

To create a one-to-one relationship:

  • Open the desired database.

  • Click the Database Tools tab.

  • Click the Relationships button in the Relationships group.

    continues on next slide…

Relationships button


Create a one to one relationship continued1
Create a One-to-One Relationship…continued

  • At the Show Table dialog box, click the first table name.

  • Click the Add button.

  • Repeat Steps 4 and 5 until all desired table names have been added.

  • Click the Close button.

    continues on next slide…

Show Table dialog box


Create a one to one relationship continued2
Create a One-to-One Relationship…continued

  • At the Relationships window, drag the common field to the related table.

    continues on next slide…

Relationships window


Create a one to one relationship continued3
Create a One-to-One Relationship…continued

  • At the Edit Relationships dialog box, check to make sure the correct field name displays and the relationship type displays as One-To-One.

  • Specify the relationship options.

  • Click the Create button.

Edit Relationships dialog box


Display related records in a subdatasheet
Display Related Records in a Subdatasheet

  • When a relationship is established between tables, you can view and edit records in related tables with a subdatasheet.

subdatasheet


Display related records in a subdatasheet continued
Display Related Records in a Subdatasheet…continued

To display a subdatasheet:

  • Click the expand indicator at the left side of the desired record.

  • Click the desired table at the Insert Subdatasheet dialog box.

Insert Subdatasheet dialog box


Display related records in a subdatasheet continued1
Display Related Records in a Subdatasheet…continued

To display subdatasheets for all records:

  • Click the More button in the Records group in the Home tab.

  • Point to Subdatasheet.

  • Click the Expand All option.

Expand All option


Display related records in a subdatasheet continued2
Display Related Records in a Subdatasheet…continued

To remove a subdatasheet:

  • Click the More button in the Records group in the Home tab.

  • Point to Subdatasheet.

  • Click the Remove option.

Remove option


Checkpoint 2
CHECKPOINT 2

  • This type of relationship is used when you want to break a large table into two smaller tables.

    • many-to-many

    • many-to-one

    • one-to-many

    • one-to-one

  • To display a subdatasheet, click this preceding the record.

    • view indicator

    • expand indicator

    • collapse indicator

    • remove indicator

Answer

Answer

Next Question

Next Question

  • Add tables to the Relationships window at this dialog box.

    • Show Table

    • Show Relationships

    • Edit Relationships

    • Edit Table

  • To display all subdatasheets, start by clicking this button in the Home tab.

    • View

    • Expand

    • Display

    • More

Answer

Answer

Next Question

Next Slide


Creating relationships between tables1
Creating Relationships between Tables

Summary of Presentation Concepts

  • Define a primary key in a table

  • Create a one-to-many relationship

  • Specify referential integrity

  • Print, edit, and delete relationships

  • Create a one-to-one relationship

  • View and edit a subdatasheet


ad