isds 351

Tonyttttt

Lecture08-UsingMSAccess.pptx

Home >Business & Finance homework help >isds 351

Using Microsoft Access 2016

Study Questions

Q1: How do you create tables?

Q2: How do you create relationships?

Q3: How do you create a data entry form?

Q4: How do you create queries using the query design tool?

Q5: How do you create a report?

CE5-‹#›

This chapter extension discusses fundamental techniques for creating a database and a database application with Microsoft Access 2010, 2016.

PROSPECT (ProspectID, Name, Street, City, State, Zip, Email Address)

WORK (ProspectID, Date, Hour, NumCalls, TotalDonations)

Create tables from data model

Select primary keys

Select foreign keys

Link tables

Q1: How Do I Create Tables?

CE5-‹#›

Note the ambiguity in name PROSPECT. Before someone has become a volunteer, he is a prospect, and the term is fine. However, once that person has actually done work, he is no longer merely a prospect. This ambiguity occurs because the database is used both for finding volunteers and for recording their experiences once they have joined.

Attributes of the Database

CE5-‹#›

This figure further documents the attributes of the design. Sample data for this table are shown in Figure CE5-2.

Sample Data

CE5-‹#›

Opening Access 2016

CE5-‹#›

If you use another version of Access, your screen will appear differently, but the essentials will be the same.

Opening Access 2016

CE5-‹#›

If you use another version of Access, your screen will appear differently, but the essentials will be the same.

Creates Default Table Named Table1

CE5-‹#›

Access opens the new database by creating a default table named Table1. We want to modify the design of this table, so in the upper left-hand corner, where you see a pencil and a right angle square, click View and select Design View.

Click on Blank database

Enter file name in lower right-hand corner box

Click on folder icon to browse to a folder to save database

Click on Create

Blank table opens

Review: Sequence of Steps for Creating a Database with Access 2016

CE5-‹#›

Use this figure to review the steps presented in previous slides.

Creating a Database with Access 2016 (cont’d)

Click View icon

Select Design View in drop-down list

Enter table name in pop-up box

Window changes to Design View

CE5-‹#›

Explain Access View modes

Creating Tables in Access - Step 1: Declaring Field Names and Data Types

Upper part

Enter name of each attribute (Fields)

Enter Data Type

Enter Description

Bottom part

Declare field properties

CE5-‹#›

Creating Tables in Access - Step 2: Declaring Field Properties

Setting length of a Text Data Type

CE5-‹#›

Figure shows how to set the length of a Text Data Type.

Complete Sample PROSPECT Table

CE5-‹#›

ProspectID is the primary key of this table, and the little key icon next to the ProspectID Field Name means Access has already made it so. If we wanted to make another field the primary key, we would highlight that field and then click the Primary Key icon in the left-hand portion of the Design ribbon.

Finished WORK Table

CE5-‹#›

Save table

Select Primary Key

Declare Field Properties

Enter Data Types

Enter Field Names

Overview of Steps in Creating Tables

CE5-‹#›

Go to Database Tools

Select Relationships

Show Table dialog box opens

Double-click on names of tables

Tables added to Relationships window

Close Show Table dialog box

Q2: How Do I Create Relationships? Step 1

CE5-‹#›

Show Table Dialog Box in Access

CE5-‹#›

Click the Database Tools tab in the ribbon and then click the Relationships icon near the left-hand side of that ribbon. The Relationships window will open and the Show Table dialog box will be displayed.

Click on attribute in one table

Drag it to attribute in second table

Dialog box appears

Click Enforce Referential Integrity

Click Cascade Update Related Fields

Click Cascade Delete Related Fields

Close Relationships window

Save changes

Creating a Relationship Between Two Tables: Step 2

CE5-‹#›

Creating a Relationship Between Two Tables: Step 2 (cont’d)

CE5-‹#›

Click Enforce Referential Integrity, click Cascade Update Related Fields, and then click Cascade Delete Related Records. The specifics of these actions are beyond the scope of our discussion. Just understand that clicking these options will cause Access to make sure that ProspectID values in WORK also exist in PROSPECT.

Completed Relationship Between PROSPECT and WORK Tables

∞ means one PROSPECT row can be related to an unlimited number of rows in WORK

CE5-‹#›

PROSPECT Table with Data Entered

CE5-‹#›

WORK Table with Data Entered

CE5-‹#›

Q3: How Do I Create a Data Entry Form?

Use default table display

CE5-‹#›

Table display, although convenient, is limited in capability. It also does not provide a very pleasing user interface. For more generality and better design, you can use the Access form generator.

Form Generator

Starting the Form Generator

CE5-‹#›

Resulting Data Entry Form

CE5-‹#›

Access uses metadata about the tables and their relationship to create the data entry form in Figure CE5-15.

Reformatted Data Entry Form

CE5-‹#›

Process for Reformatting Data Entry Form

CE5-‹#›

Use SQL query language

Use graphical interface to create queries

Click Queries

Click Query Design

Show Table, Add

Query Design form

Q4: How Do You Create Queries Using the Query Design Tool?

Run !

CE5-‹#›

Like all modern DBMS products, Access can process the SQL query language.

Creating a Query: Step 1 (Select and Add Tables)

CE5-‹#›

Step 2: Query Design Form

CE5-‹#›

Notice that Access shows the relationship between the two tables.

Creating a Query: Step 3

CE5-‹#›

Note that the Ascending keyword has been selected for the Name column.

Click the red exclamation point labeled Run in the Results section of the ribbon.

Results of TotalDonations Query

CE5-‹#›

Results of More Advanced Query

CE5-‹#›

Click Create

Select Report Wizard

Table/ Queries

Highlight table and fields

Click >

Add fields

Select table, add fields

Click Finish

Q5: How Do I Create a Report?

CE5-‹#›

Selecting Data to Show in a Report

CE5-‹#›

This figure shows the Report Wizard. To create a report with data from two or more tables, we must use the Report Wizard.

Click the Create tab, and then in the Reports section click Report Wizard.

Report on Donations by Prospect

CE5-‹#›

Report Design View

CE5-‹#›

Click Group & Sort in Grouping & Totals

Click More

Click down arrow to “with no totals”

Click Report icon in View

See result next slide

Grouping and Totaling

CE5-‹#›

Creating a Sum of TotalDonations for Each Prospect

CE5-‹#›

In the ribbon, click Group and Sort in the Grouping & Totals section. In the bottom of the form, click More, and then click the down arrow next to the phrase “with no totals.” Next, select TotalDonations from the TotalOn box, and then check Show Grand Total and Show subtotal in group footer.

Report with Sum of TotalDonations

CE5-‹#›

The only remaining problem is that label NumCalls is cut off. We need to expand the box that contains this value. To do so, select Layout View from View in the ribbon, click Date, and then slide it slightly to the left. Do the same with Hour. Then expand NumCalls until you can see all of the label.

Increasing Size of Fields

Select Layout View from View in the ribbon, click Date. Slide it slightly to left. Do same with Hour. Expand NumCalls.

CE5-‹#›

Final Version of Report

CE5-‹#›

Active Review

Q1: How do you create tables?

Q2: How do you create relationships?

Q3: How do you create a data entry form?

Q4: How do you create queries using the query design tool?

Q5: How do you create a report?

CE5-‹#›