Summarize the three most important things you have learned in this pdf.
papi2lit
M01_Access2016.pptxShelly Cashman: Microsoft Access 2016
Module 1: Databases and Database Objects: An Introduction
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Objectives (1 of 2)
Describe the features of the Access window
Create a database
Create tables in Datasheet and Design views
Add records to a table
Close a database
Open a database
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Objectives (2 of 2)
Print the contents of a table
Create and use a query
Create and use a form
Create and print custom reports
Modify a report in Layout view
Perform special database operations
Design a database to satisfy a collection of requirements
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Project – Database Creation
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Figure 1-1a: display of account table
Figure 1-1b: display of account manager table
4
Roadmap
Create the first table, Account Manager, using Datasheet view
Add records to the Account Manager table
Print the contents of the Account Manager table
Import records into the second table, Account
Modify the second table using Design view
Create a query for the Account table
Create a form for the Account table
Create a report for the Account table
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Creating a Database (1 of 2)
To Create a Database
Run Access
Using the steps in the “To Create an Access Database” section in the Office and Windows module, create the database on your hard disk, OneDrive, or other storage location using the desired file name
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Figure 1-2: display of how to create a database
6
Creating a Database (2 of 2)
To Create a Database Using a Template
If you have another database open, close it withot exiting Access by clicking File on the ribbon to open the Backstage view, and then clicking Close
If you do not see a template that you want, you can search Microsoft Office onlin for additional templates
Click the template you want to use. Be sure you have selected one that indicates it is for a desktop database
Enter a file name and select a location for the database
Click the Create button to create the database
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
7
The Access Window
Navigation Pane and Access Work Area
Define the following
Access work area
Object tabs
Navigation Pane
Status bar
Datasheet view
Datasheet
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
8
Determining Tables and Fields (1 of 2)
Naming Tables and Fields
Names can be us to 64 characters in length
Names can contain letters, digits, and spaces, as well as most of the punctuation symbols
Names cannot contain periods (.), exclamation point (!), accent graves (`), or square brackets ([])
Each field in a table must have a unique name
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
9
Determining Tables and Fields (2 of 2)
Determining the Primary Key
Unique identifier
Field called ID
Autonumber field
Determining Data Types for the Fields
Data type
Short Text
Number
Currency
Date & Time
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
10
Creating a Table (1 of 11)
Datasheet view – rows and columns, like a spreadsheet
Design view – only used to create a table or to modify the structure of a table
Caption – an assigned value to a field
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Creating a Table (2 of 11)
To Modify the Primary Key
Click the Field Size text box to select the current field size, use either the DELETE or BACKSPACE keys to erase the current field size, and then type the new field size
Click the Name & Caption button to display the Enter Field Properties dialog box
Click the Caption text box, and then type the desired caption
Click the Description text box, and then type the desired description
Click the OK button to change the caption and description
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Creating a Table (3 of 11)
To Define the Renaming Fields in a Table
Click the ‘Click to Add’ column heading to display a menu of available data types
Click the desired data type
Type the desired field name
Click the blank space below the field name to complete the change of the name. Click the blank space a second time to select the field
Change the field size, if necessary
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Creating a Table (4 of 11)
Click the Save button on the Quick Access Toolbar to display the Save As dialog box
Type the desired table name
Click the OK button (Save As dialog box) to save the table
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Figure 1-13: display using the views group and the change in the table name
14
Creating a Table (5 of 11)
To View the Table in Design View
Click the View button arrow to display the View menu
Click Design View on the View menu to view the table in Design view
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Figure 1-15: The design view is displayed
15
Creating a Table (6 of 11)
To Change a Field Size in Design View
If necessary, click the vertical scroll bar to display the desired field. Click the row selector for the desired field to select the field
Click the Field Size box to display the Field Size arrow
Click the Field Size arrow to display the Field Size menu
Click the desired field size
Click the Format box to display the Format arrow
Click the Format arrow to open the Format menu
Click the Save button to save your changes
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Creating a Table (7 of 11)
To Close the Table
Click the Close button for the open table to close the table
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Creating a Table (8 of 11)
To Add Record to a Table
Right-click the table in the Navigation Pane to display the shortcut menu
Click Open on the shortcut menu to open the table in Datasheet view
Click the Shutter Bar Open/Close Button to close the Navigation Pane
Type the desired values in each field, pressing the TAB key to move to the next field
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Creating a Table (9 of 11)
Making Changes to the Data
Undo
Add a record
Delete a record
Change the contents of a field
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Creating a Table (10 of 11)
To Add Records to a Table that Contains Data
Run Access
Open desired database
Open the Navigation Pane
Click Open on the shortcut menu to open the table in Datasheet view
Close the Navigation Pane
Click the ‘New (blank) record’ button to move t a position to enter a new record
Close the table
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Creating a Table (11 of 11)
To Resize Columns in a Datasheet
Point to the right boundary of the field selector so that the mouse pointer becomes a two-headed arrow
Double-click the right boundary of the field selector to resize the field so that it best fits the data
Save the changes to the layout by clicking the Save button on the Quick Access Toolbar
Click the table’s Close button to close the table
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Previewing and Printing the Contents of a Table
To Preview and Print the Contents of a Table
If necessary, open the Navigation Pane and select the table you wish to preview and print
Click FILE on the ribbon to open the Backstage view
Click the Print tab in the Backstage view to display the Print gallery
Click the Print Preview button in the Print gallery to display a preview of what the table will look like when printed
Click the Print button to display the Print dialog box
Click the OK button to print the table
When the printer stops, retrieve the hard copy
Click the Close Print Preview button to close the Print Preview window
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Importing or Linking Data From Other Applications to Access (1 of 3)
To Import an Excel Worksheet
Click EXTERNAL DATA on the ribbon to display the EXTERNAL DATA tab
Click the Excel button to display the Get External Data – Excel Spreadsheet dialog box
Click the Browse button in the Get External Data – Excel Spreadsheet dialog box
Navigate to and select the desired file
Click the Open button
With the option button to import the source data to a new table elected, click the OK button to display the Import Spreadsheet Wizard dialog box
Be sure the ‘First Row Contains Column Headings’ check box is selected
Click the Next button
Click the ‘Choose my own primary key’ option button
Click the Finish button
Click the Save Import button to save the import steps
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Importing or Linking Data From Other Applications to Access (2 of 3)
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Figure 1-45: click the next button
24
Importing or Linking Data From Other Applications to Access (3 of 3)
To Modify a Table in Design View
Right-click the desired table in the Navigation Pane to display the shortcut menu, and then click Design View on the shortcut menu to open the table in Design View
Click the Description box for the desired field and then type the desired text
Click the Save button on the Quick Access Toolbar to save your changes
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Additional Database Objects (1 of 6)
To Use the Simple Query Wizard to Create a Query
If necessary, open the Navigation Pane
Select the table you wish to query
Click CREATE on t he ribbon to display the CREATE tab
Click the Query Wizard button to display the New Query dialog box
Be sure Simple Query Wizard is selected, and then click the OK button (New Query dialog box) to display the Simple Query Wizard dialog box
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Figure 1-55: add selected fields
26
Additional Database Objects (2 of 6)
To Use a Criterion in a Query
Right-click the query to open in the Navigation Pane to produce a shortcut menu
Click Design View on the shortcut menu to open the query in Design view
Click the Criteria row in the column for the field for which you want to specify the criteria, and then type the criteria
Click the Run button to run the query and display the results in Datasheet view
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Additional Database Objects (3 of 6)
To Create a Form
Select the table in the Navigation Pane for which you want to create a form
If necessary, click CREATE on the ribbon to display the CREATE tab
Click the Form button to create a simple form
Click the Form View button on the Access status bar to display the form in Form view
Click the Next record button to move to the next record
Click the Save button on the Quick Access Toolbar to display the Save As dialog box
Type the desired form name, and then click the OK button to save the form
Click the Close button for the form to close the form
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Additional Database Objects (4 of 6)
To Create a Report
Select the table in the Navigation Pane for which you want to create the report
Click CREATE on the ribbon to display the CREATE tab
Click the Report button to create the report
Click the Save button on the Quick Access Toolbar to display the Save As dialog box and then type the name of the report
Click the OK button (Save As dialog box) to save the report
Close the report by tapping or clicking its Close button
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Additional Database Objects (5 of 6)
To Modify Report Column Headings and Resize Columns
Right-click the report in the Navigation Pane you wish to modify and then click Layout View on the shortcut menu
If a Field list appears, click the Add Existing Fields button to remove the Field list from the screen
Close the Navigation Pane
Click the column heading you wish to modify two times
Type the new column heading
Point to the right boundary of the heading for the column you wish to resize until the mouse pointer changes to a two-headed arrow and then drag the right boundary to the desired position
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Additional Database Objects (6 of 6)
To Add Totals to a Report
Click the field you want to total
Click DESIGN on the ribbon to display the DESIGN tab
Click the Totals button to display the list of available calculations
Click Sum to calculate the sum of the amount of paid values
Click the Save button on the Quick Access Toolbar to save your changes to the report layout
Close the report
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Database Properties
To Change Database Properties
Click FILE on the ribbon to open the Backstage view
If necessary, click the Info tab in the Backstage view to display the Info gallery
Click the ‘View and edit database properties’ link in the right pane of the Info gallery
Enter the desired database properties
Click the OK button to save your changes
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Special Database Operations (1 of 3)
To Back Up a Database
Open the database to be backed up
Click FILE on the ribbon to open the Backstage view, and then tap or click the Save As tab
With Save Database As selected in the File Types area, click ‘Back Up Database’ in the Save Database As area, and then click the Save As button
Navigate to the desired location in the Save As box. If you do not want the name Access has suggested, enter the desired name in the File name text box
Click the Save button to back up the database
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Special Database Operations (2 of 3)
To Compact and Repair a Database
Open the database to be compacted
Click FILE on the ribbon to open the Backstage view, and then, if necessary, select the Info tab
Click the ‘Compact & Repair Database’ button in the Info gallery to compact and repair the database
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Special Database Operations (3 of 3)
Additional Operations
Closing a database without exiting Access
Saving a database with another name
Deleting a table or other object in the database
Renaming an object in the database
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
Database Design
Database Design Process
Identify the Tables
Determine the Primary Keys
Determine Additional Fields
Determine and Implement Relationships between the Tables
Assign Data Types to the Fields
Identify and Remove Redundancy
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.
‹#›
