summarize access ch 2
Kazo7777
a02_powerpoints.pptx1
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
by Mary Anne Poatsy, Keith Mulbery, Lynn Hogan, Amy Rutledge, Cyndi Krebs, Eric Cameron, Rebecca Lawson
Chapter 2
Tables and Queries in Relational Databases
exploring Microsoft Office 2013 Volume 1
In this chapter, you will be learning the basic principles of table and query design.
You will also learn how to use tables and forms to input data, and how to create queries and reports to extract information from a database in a useful and organized way.
1
Objectives
Design a table
Create and modify tables
Share data
Establish table relationships
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
2
The objectives of this chapter are:
Design a table
Create and modify tables
Share data
Establish table relationships
The objectives continue on the next slide.
2
Objectives (cont.)
Create a single-table query
Specify query criteria for different data types
Understand query sort order
Run, copy, and modify a query
Use the Query Wizard
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
3
Additional objectives include to:
Create a single-table query
Specify query criteria for different data types
Understand query sort order
Run, copy, and modify a query
Use the Query Wizard
The objectives continue on the next slide.
3
Objectives (cont.)
Create a multitable query
Modify a multitable query
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
4
Additional objectives include:
Create a multitable query
Modify a multitable query
4
Table design
Must have a good database design
Systematic approach
List all of the tables needed
List all of the fields needed
5
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
In the previous chapter you learned that a table is a collection of records, with each record made up a number of fields. Good database design begins with the tables. Tables provide the framework for all of the activities you perform in a database. If the table’s framework is poorly designed, the rest of the database will be poorly designed.
When creating tables for a database, a system should be followed. The first step is to list all of the tables you need for the database and list all the fields in each table. During the table design process, think of the specific fields you need in each table; list the fields under the correct table and assign each field a data type.
The order of the fields and specific names are not important, because they will be changed later. It is very important that the tables contain all of the necessary fields so that the system can produce the required information.
In this chapter you will learn how to refine tables by changing the properties of various fields.
5
Designing field guidelines
Include the necessary data
Design for now and future
Store data in smallest parts
Add calculated fields
Design to accommodate date arithmetic
Link tables using common fields
6
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
After the tables have been identified, add the necessary fields using these six guidelines:
Include the necessary data
Design for now and for the future
Store data in their smallest parts
Add calculated fields
Design to accommodate arithmetic
Link tables using common fields
Each of these guidelines will be discussed in the following slides.
6
Include necessary data
7
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
A good way to determine what data are necessary in tables is to consider the output you need. It will probably be necessary for you to create professional-looking reports for others. Design tables that contain the fields necessary to create those reports needed. You will need to ask yourself what information will be expected from the system and determine the data required to produce that information. This figure shows a customer table and two other tables found in a sample bank database. It also lists fields that would be needed in each table record.
7
Design for now and for the future
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
8
Organizations evolve over time
Anticipate future needs of the organization
As the data requirements of an organization evolve over time, the information systems that hold the data must change as well. When designing a database, try to anticipate the future needs for the system and build in the flexibility to satisfy those demands.
A good database design must balance the data collection needs of the company with the cost associated with collection and storage. Plans must also include the frequency and cost necessary to modify and update the database.
8
Store Data in their smallest parts
Creating a customer name as one field is limiting
Lack of flexibility
Cannot be easily alphabetically displayed
9
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
It seems like it would make more sense to create one field for a customer’s name in a database. This one field consisting of both the first and last name, but the approach is too limiting. Dividing the customer’s name into two fields (FirstName and LastName) would reference each individually.
The first problem with in this approach is lack of flexibility. You could not easily create a salutation for a letter of the form because the first and last name are not accessible individually.
The second issue is that the list of customers cannot be easily displayed in alphabetical order by last name because the last name begins in the middle of the field. For example, if the fields were not created separately, the names could easily be alphabetized by first name because the first name is at the beginning of the field. But the most common way to sort names is by the last name, which can be done more efficiently if the last name is stored as a separate field.
9
Add calculated fields to a table
Produces a value from an expression or function
References one or more existing fields
Calculated fields are stored in a table
Enables adding data to:
Queries
Forms
Reports
10
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
A calculated field produces a value from an expression or function that references one or more existing fields. Access enables you to store calculated fields in a table using the calculated data type.
An example of a calculated field in the bank database would be the interest it pays its customer each month at a rate of 1.0% interest on the principal. A calculated field such as Monthly Interest could store the expression Principal x 0.01.
Storing calculated data in a table enables you to add the data easily to queries, forms, and reports without the trouble of an additional calculation. Storing calculated data in a table might increase the size of the database slightly, but the benefits could outweigh this drawback.
10
Design to accommodate date arithmetic
11
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
Calculated fields created with numeric data
Calculated fields created using date/time data
Calculated fields are frequently created with numeric data. You can also create calculated fields using date/time data.
11
Link tables using common fields
12
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
As you create the tables and fields for the database, keep in mind that the tables will be joined in relationships using common fields. Draw a line between common fields to indicate the joins. In this figure, the joined lines show the relationships between the tables. You should make sure that common fields are named the same and have the same data type.
Avoid data redundancy, which is the unnecessary storing of duplicate data in two or more tables. You should avoid duplicate information in multiple tables in a database because errors can occur.
12
Creating tables
Tables can be created by:
Typing field name in Design view
Entering table data in Datasheet view
Importing data from another database or application
13
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
Tables can be created in a new blank database or in an existing database. You can create a table by:
Typing a field name in a row in Design view
Entering table data into a new row in Datasheet view
Importing data from another database or application such as Excel
A table can always be modified to include a new field or change an existing field.
13
Creating a table in design view
14
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
When you add a new field in Design view, the field must be given a field name to identify the data it holds. The field name should be descriptive of the data and can be up to 64 characters in length, including letters, numbers, and spaces. Database developers use CamelCase notation for field names. Instead of spaces in multiword fields names, use uppercase letters to distinguish the first letter of each new word, for example, ProductCost or LastName.
Field names can be renamed or deleted in either Design view or Datasheet view.
14
Data types
15
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
Every field has data type. The data type property determines the data type and the operations that can be performed on the data. Access recognizes 12 data types, including:
Short text: Stores alphanumeric data
Long text: Stores lengthy text
Number: Contains values that can be used in calculations
Date/Time: Holds dates or times
Currency: Contains monetary values
AutoNumber: A special data type used to assign the next consecutive number each time you add a record
Yes/No: Assumes one of two value Yes/No, True/False, On/Off (also known as Boolean)
OLE object: Contains an object created by another application
Hyperlink: Stores a Web address (URL) or the path to a folder or file
Attachment: Used to store multiple images, spreadsheet files, Word documents, and other type of supported files
Calculated: The results of an expression that references one or more existing fields
Lookup Wizard: Creates a field that enables you to choose a value from another table
15
Primary and Foreign keys
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
16
In Chapter 1, we learned that the primary key is the field that uniquely identifies each record in a table. Access does not require that each table have a primary key. However a good database design usually includes a primary key in each table. When no primary key occurs, you can create a primary key with the AutoNumber data type. The AutoNumber data type is a number that automatically increments each time a record is added.
A foreign key is a field in one table that is also the primary key of another table.
16
Field properties
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
17
The field’s data type determines the type of data that can be entered and the operations that can be performed on that data, and its field properties determine how the field looks and behaves. The field properties are set to default values according to the data type.
The field size determines the maximum number of characters in the field. For an example, a field with a short text data type can hold up to 255 characters. A field with a number data type can be set to integer to display the field contents as integers.
You can set a caption property to create a label more readable than a field. The caption displays at the top of a table or query column in the Datasheet view and when the field is used in a report or form.
The validation rule checks the data entered when the user exits the field. If the data entered violates the validation rule, an error message displays and prevents the invalid data from being entered into the field.
The Design view is used to create and modify the table structure by enabling you to add and edit a field and set field properties.
17
Import an excel spreadsheet
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
18
Most companies store some sort of data in Excel spreadsheets. The data stored in Excel can be managed much more efficiently in Access. Access provides a wizard that guides you through the process of importing data from Excel.
The first step in importing an Excel spreadsheet is to launch the feature by clicking the External Data tab and clicking Excel in the Import & Link Group. Locate the Excel file you want to import by clicking Browse. Then choose among three options for incoming data:
Import the source data into a new table in the current database. This stores a copy of the data in Access.
Append a copy of the records to the table. This adds the data to an existing table.
Link to the data source by creating a linked table. This creates a link to the Excel source.
After the Excel workbook is located and selected, accept the default option (Import the source data into a new table in the current database) and click OK. Select the worksheet you want to import and click Next.
18
Import an Excel Spreadsheet (Cont.)
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
19
The Accounts worksheet is selected. The bottom of the Import Spreadsheet Wizard dialog box displays a preview of the data stored in the specified worksheet. Click Next to continue.
19
Import an Excel Spreadsheet (Cont.)
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
20
Not all spreadsheets are ready to import. The spreadsheet might have to be revised before importing. The second window of the Import Spreadsheet Wizard dialog box contains a check box that enables you to convert the first row of column headings to field names in Access. This check box only needs to be selected if a column heading row exists in the spreadsheet. If no column headings exist, leave the check box cleared and the data will import using Field1, Field2, Field3, and so on, as the field names.
20
21
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
Import an Excel Spreadsheet (Cont.)
The next window of the Import Spreadsheet Wizard dialog box enables you to specify field options. The AID field is highlighted in this figure. It will become the table’s primary key, you need to set the Indexed Property to Yes(No Duplicates). To modify the field option of the other fields, click the Field Name column heading and make changes. Not all Access table properties are supported by the wizard. Some changes will have to be made in the Table Design view.
21
Import an Excel Spreadsheet (Cont.)
22
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
The next window of the Import Spreadsheet Wizard dialog box enables you to choose a primary key before the actual import takes place. If the option Let Access add primary key is selected, Access will generate an AutoNumber field and designate it as the primary key. You can designate a field to be the primary key or choose to have no primary key.
22
Import an Excel Spreadsheet (Cont.)
23
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
The final window of the Import Spreadsheet Wizard is used to name the Access table. If the worksheet in the Excel workbook was named, Access uses the worksheet name as the table name.
The Wizard will ask if you wish to save the import steps. Saving the import steps will help you import the data the next time it is needed.
23
Establishing Table Relationships
24
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
The benefit of a relationship is to efficiently combine data from related tables for the purpose of creating queries, forms, and reports. Once you have created tables by storing like data items together, you will be able to recognize that some tables have common fields with others. This figure shows the Bank database with relationships created by joining common fields.
24
Referential Integrity
25
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
Referential integrity enforces rules in a database that are used to preserve relationships between tables when records are changed.
When referential integrity is enforced, you cannot enter a foreign key in a related table unless the primary key value exists in the primary table. When referential integrity is enforced, you cannot delete a record in one table if it has related records.
25
Cascade Options
26
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
When you create a relationship in Access and select the Enforce Referential Integrity check box, Access gives you two additional options: Cascade Update Related Fields and Cased Delete Related Records. Select the Cascade Update Relate Fields check box so that when the primary key is modified in a primary table, Access will automatically update all foreign key values in a related table.
The Cascade Delete Related Records option will allow Access to automatically delete all records in related tables that reference the primary key, when the primary key is deleted in a primary table.
26
Relationship types
27
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
Access provides three different relationships for joining your data: one-to-one, one-to-many, and many-to-many.
The most common type of relationship is the one-to-many relationship. A one-to-many relationship is established when the primary key value in the primary table can match many of the foreign key values in the related table.
An example of a one to many relationship would be one branch has many customer.
27
Creating a single-table query
Can be created two ways:
Query Design tool
Simple Query Wizard
28
A query enables you to ask questions about the data stored in a database and then provides the answers to the questions by providing subsets or summaries of data.
You can create a single-table query in two ways: using the Simple Query Wizard or the Query Design tool in the Queries group on the Create tab.
The Query Design tool is the most flexible way to create a query. The Query Design tool is used to create select queries. A select query is a type of query that displays only the records that match criteria entered in the Query Design view. To create a select query using the Query Design tool, do the following steps:
Click the CREATE tab.
Click Query Design in the Queries group.
Select the table you need in your query from the Show Table dialog box.
Click Add to add the table to the top section of the query design and close the Show Table dialog box.
Drag the fields needed from the table to the query design grid (alternatively double-click the field names) then add criteria and sorting options.
Click Run in the Results group to show the results in Datasheet View
28
Query Design View
29
The Query Design view is used to create queries. The Query Design view is divided into two parts:
The top portion displays the tables.
The bottom portion (query design grid) displays the fields and the criteria.
The top portion contains tables with their respective field names. If a query contains more than one table, the join lines between tables will be displayed as they were created in the Relationships window.
The bottom portion is known as the query design grid. It contains columns and rows. Each field in the query has its own column and contains multiple rows. The rows permit you to control the query results.
29
Query Datasheet View
30
When you create queries, you toggle between Design view and Datasheet view, just as you would with tables. Use Design view to specify the criteria; your results will be displayed in Datasheet view.
The criteria of the query is shown in Design view, as seen in the top image.
The results of the query are shown in Datasheet view, as seen in the bottom image.
30
Parts of Query Design View
Field Row
Table Row
Sort Row
Show Row
Criteria Row
31
The Field row displays the field name.
The Table row displays the data source.
The Sort row enables you to sort in ascending or descending order.
The Show row controls whether the field will be displayed in the query results.
The Criteria row is used to set the rules that determine which records will be selected.
31
Specifying query criteria for different data types
Delimiter might be needed
Delimiter automatically added by Access for some data types
32
When specifying the criteria for a query, you might need to include a delimiter. A delimiter is a special character that surrounds a criterion’s value. The delimiter that is needed is determined by the field data type, and Access will automatically enter the delimiter for you for some data types.
Text fields require quotation marks before and after the text. Access automatically adds the quotation marks around text.
Numeric, currency, or AutoNumber fields use plain digits for the criteria. You can enter numeric criteria with or without a decimal point and with or without a minus sign. Commas and dollar signs are not allowed.
Date fields enclose the criterion in pound signs. Access accepts a date with or without the pound signs.
32
Wildcards
33
Wildcards are special characters used to represent one or more characters in a text value.
A question mark is a wildcard that stands for a single character in the same position as the question mark.
An asterisk is a wildcard that stands for any number of characters in the same position as the asterisk.
Brackets is a wildcard that is used to match any single character within the brackets.
An exclamation mark is a wildcard used inside brackets to match any character not in the brackets.
The pound sign is used to match any single numeric character.
Wildcard characters are entered in the Criteria row of a query.
33
Comparison operators
34
A comparison operator such as equal (=), not equal (<>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to(<=) can be used in the criteria of a query.
Comparison operators enable you to limit the query results to only those records that meet the criteria.
34
Null
35
The term that Access uses to find a blank field is null. The two null expressions are null and is not null. Is Not Null is used to find fields with data.
35
And, or, and not criteria
36
The logical operators are used when questions are more specific and require queries with multiple criteria.
When the criteria are in the same row of the query design grid, Access interprets the instructions using the AND operator. This means that the query results will display on the records that match all criteria.
When you have multiple sets of criteria and you need to satisfy one set only, use the OR criterion. The query results will display records that match any of the specified criteria.
The NOT logical operator returns all records except the specified criteria.
36
Query sort order
Determines the order of records
Sort order can be specified
Determined from left to right
37
The query sort order determines the order of records in a query’s Datasheet view. You can change the order of records by specifying the sort order in Design view. When you want to sort using more than one field, the sort order is determined from left to right. The order of columns should be considered when first creating the query.
37
Running a query
Click Run in Design view
Double-click in the Navigation Pane
38
There are several ways to run a query. One method is to click Run in the Results group when you are in Design view. Another method is to locate the query in the Navigation Pane and double-click it.
38
SIMPLE QUERY Wizard
39
The Simple Query Wizard guides you through the query design process. The wizard is helpful for creating basic queries that do not require criteria. After the query is creating using the Wizard, you can switch to Design view and add criteria manually.
To launch the Query Wizard, click the Create tab and click Query Wizard in the Queries group.
Next, select the Simple Query Wizard in the Query Wizard dialog box.
39
SIMPLE QUERY wizard (cont.)
40
In the first step of the Simple Query Wizard dialog box, you specify the tables or queries and fields needed in your query. When you select a table form the Tables/Queries arrow, a list of the table’s fields displays in the Available Fields list box.
40
SIMPLE QUERY wizard (cont.)
41
Select the necessary fields and move them to the Selected Fields list box using the directional arrows.
41
SIMPLE QUERY wizard (cont.)
42
Choose between a detail and a summary query in the next step of the Wizard. The detail query shows all fields of every record in the result.
The summary query enables you to group data in summary records.
42
SIMPLE QUERY wizard (cont.)
43
The final dialog box of the Simple Query Wizard asks for the name of the query. Assign a descriptive name to your queries.
43
Multitable Queries
Contains two or more tables
Uses related tables
Similar to creating single-table query
44
Multitable queries contain two or more tables. They enable you to take advantage of the relationships that have been set in your database. When you need to extract information from a database with a query, most times you will need to pull the data from multiple tables to provide the answers you need. One table might contain core information that you need, whereas another table might contain the related data that makes the query relevant to the users.
Creating a multitable query is similar to creating a single-table query. The right tables must be chosen and the table relationship will need to managed properly. Only include related tables in a multitable query. Related tables are tables that are joined in a relationship using a common field. Related tables should already be established when you create a multitable query.
Modifying a multitable query can be done in Design view just like a single-table query. Additional tables can be added in Design view. Fields can also be deleted and added.
You can get key information from your database using a multitable query.
44
Summary
Table and query design
Queries answer questions
Query Wizard
Criteria for queries
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
45
The chapter introduced the concepts of a good table design and query design. When creating a table, a list of the fields needed should be known. Tables can be imported from Excel. The information for queries is based on the tables. The query answers a question. There are certain criteria that can be used when creating queries.
45
Questions
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
46
This chapter was about the organization of tables and how they are foundational to a database. In the next chapters we will go more in depth with the software.
46
Copyright
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
47
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America.
47
Microsoft®
Office 2013 VOluMe 1
Series Editor MaryAnne Poatsy Mulbery | Hogan | Rutledge | Cameron | Krebs
Series Created by Dr. Robert T. Grauer
