Transcript Microsoft Access 2010

MICROSOFT
ACCESS 2010
Chapter 1
Databases
and Database
Objects: An
Introduction
OBJECTIVES
 Design a database to
satisfy requirements
 Close a database
 Describe the features
of the Access window
 Print the contents of a
table
 Create a database
 Create and use a query
 Create tables in
Datasheet and Design
views
 Create and use a form
 Add records to a table
 Open a database
 Create and print
custom reports
 Modify a report in
Layout view
CY Lin
2
PROJECT – DATABASE CREATION
CY Lin
3
GENERAL PROJECT GUIDELINES









Identify the tables
Determine the primary keys
Determine the additional fields
Determine relationships between the tables
Determine data types for the fields
Identify and remove any unwanted redundancy
Determine a storage location for the database
Determine additional properties for fields
Determine the best method for distributing the
database objects
CY Lin
4
CREATING A DATABASE
 With a USB flash drive connected, ensure the New tab is
selected in the Backstage view and that Blank database
is selected in the New gallery
 Click the Browse button in the right pane to display the
File New Database dialog box
 Type the desired file name in the File New Database
dialog box. Do not press the ENTER key after typing the
file name
 Navigate to the desired save location
 Click the OK button
 Click the Create button in the right pane of the New
gallery
CY Lin
5
CREATING A DATABASE
CY Lin
6
MODIFYING THE PRIMARY KEY
 Right-click the column heading for the desired
primary key field (the existing ID field)
 Type the new desired name for the field
 Click the white space immediately below the field
name to complete the addition of the field
 Click the Data Type box arrow (Table Tools Fields tab
| Formatting group)
 Click the desired data type
CY Lin
7
MODIFYING THE PRIMARY KEY
 Click the Field Size text box (Table Tools Fields tab |
Properties group) to select the current field size, and
type the new field size
 Click the Name & Caption button (Table Tools Fields
tab | Properties group)
 Click the Caption text box, and then type the
desired caption
 Click the Description text box, and then type the
desired description
CY Lin
8
MODIFYING THE PRIMARY KEY
CY Lin
9
DEFINING THE REMAINING FIELDS
 Click the Click to Add column heading
 Click the desired data type
 Enter the desired field name
 Click the white space below the field name to
complete the change of the name. Click the white
space a second time to select the field
 Change the field size, if necessary
CY Lin
10
SAVING A TABLE
 Click the Save button on the Quick Access Toolbar
 Type the desired file name
 Click the OK button
CY Lin
11
VIEWING THE TABLE IN DESIGN VIEW
 Click the View button arrow (Table Tools Fields tab | Views
group)
CY Lin
12
CLOSING THE TABLE
 Click the Close button for the open table to close the table
CY Lin
13
ADDING RECORDS TO A TABLE
 Right-click the table in the Navigation Pane
 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
CY Lin
14
RESIZING 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
 Click the table’s Close button to close the table
CY Lin
15
PREVIEWING AND PRINTING THE
CONTENTS OF A TABLE
 Open the Navigation Pane and select the table you
wish to preview and print
 Click File to open the Backstage view
 Click the Print tab in the Backstage view
 Click the Print Preview button
 Click the Print button to display the Print dialog box
 Click the OK button to print the table
 Click the Close Print Preview button to close the
Print Preview window
CY Lin
16
CREATING A TABLE IN DESIGN VIEW
 Open the Navigation Pane
 Click Create on the
Ribbon
 Click the Table Design
button (Create tab |
Tables group)
 Type the desired field
name in the Field Name
column
 Click the Primary Key
button if the field is the
primary key
 Type the desired
description in the
Description column
 Type the desired field
size in the Field Size text
box
 Type the desired caption
in the Caption text box
CY Lin
17
CREATING A TABLE IN DESIGN VIEW
CY Lin
18
IMPORTING AN EXCEL WORKSHEET
 Click External Data on the Ribbon
 Click the Excel button (External Data tab | Import & Link
group)
 Click the Browse button to display the File Open dialog box
 Navigate to and select the desired file
 Click the Open button (File Open dialog box), which will return
you to the Get External Data dialog box
 Follow the remaining steps in the wizard to import the Excel
worksheet
 Choose where to import the data
 Preview the data you are importing
 Name the table (if importing to a new table)
CY Lin
19
USING THE SIMPLE QUERY WIZARD
TO CREATE A QUERY
 Open the Navigation Pane
 Select the table you wish to query
 Click Create on the Ribbon
 Click the Query Wizard button (Create tab | Queries
group) to display the New Query dialog box
 Click the OK button (New Query dialog box) to display
the Simple Query Wizard dialog box
 Follow the remaining steps in the wizard to finish
creating the query
CY Lin
20
CY Lin
21
USING A CRITERION IN A QUERY
 Right-click the query to open in the Navigation Pane
 Click Design View on the shortcut menu to open the
query
 Click the Criteria row in the column for the field for
which you want to specify the criteria, and then type
the criteria
CY Lin
22
USING A CRITERION IN A QUERY
CY Lin
23
CREATING A FORM
 Select the table in the Navigation Pane for which you
want to create a form
 Click Create on the Ribbon
 If the form appears in Layout view, click the Form
View button on the Access Status bar
 Click the Save button on the Quick Access Toolbar
 Type the desired form name, and then click the OK
button
 Click the Close button for the form to close the form
CY Lin
24
CREATING A FORM
CY Lin
25
CREATING A REPORT
 Select the table in the Navigation Pane for which you
want to create the report
 Click Create on the Ribbon
 Click the Report button (Create tab | Reports group)
to create the report
 Click the Save button and then type the name of the
report
 Click the OK button
CY Lin
26
CREATING A REPORT
CY Lin
27
MODIFYING COLUMN HEADINGS
AND RESIZING 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/lower boundary of the field you wish to
resize until the mouse pointer changes to a two -headed
arrow and then drag the boundary to the desired position
CY Lin
28
MODIFYING COLUMN HEADINGS
AND RESIZING COLUMNS
CY Lin
29
ADDING TOTALS TO A REPORT
 Select the field you want to total
 Click Design on the Ribbon
 Click the Totals button (Report Layout Tools Design
tab | Grouping & Totals group) to display the list of
available calculations
 Click Sum to calculate the sum
 Click the Save button to save your changes to the
report layout
CY Lin
30
ADDING TOTALS TO A REPORT
CY Lin
31
CHANGING DATABASE PROPERTIES
 Click File on the Ribbon to open the Backstage view
 Click the Info tab in the Backstage view
 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
CY Lin
32
BACKING UP A DATABASE
 Open the database to be backed up
 Click File on the Ribbon, and then click the Save &
Publish 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
 Selected 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
CY Lin
33
COMPACTING AND REPAIRING 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
Information gallery to compact and repair the
database
CY Lin
34
ADDITIONAL OPERATIONS
 Opening another database
 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
CY Lin
35