Exploring Microsoft. Office Access 2007. Copyright © 2010 Pearson Education, ...
Backup, compact, and repair Access files. ▫ Create filters. ▫ Sort table data on ...
Objectives
Exploring Microsoft Office Access 2007 Chapter 1: Finding Your Way Through a Database Robert Grauer, Keith Mulbery, Maurie Wigman Lockley
Explore, describe, navigate the objects in an Access Database Understand differences in storage and memory Practice good file management Backup, compact, and repair Access files Create filters Sort table data on one or more fields
Committed
to
Shaping
the
Next
Generation
of
IT
Experts.1
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Objectives (continued)
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
2
Open a Database
Know when to use Access or Excel to manage data Use the relationship window Understand relational power
Open Recent Documents list
3
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Open a Database
Choose Open to browse for a file or choose a database from the Recent Documents list Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
4
Database Terminology Field Record Table Database
A database is made up of one or more tables
Individual fields
Open Recent Database list Individual tables in a database
Choose a database from the Open Recent Database List or click More to browse for other databases Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Records
5
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
6
1
Objects
Work with Table Views Design View
Tables Queries Reports Forms Modules Macros
Datasheet View
Datasheet View – used to add, modify, delete and view records Design View – used to create and modify the fields in a table
Objects 7
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Datasheet View
8
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Design View Key symbol identifies primary key field
Primary key field
Navigation buttons
Navigation bar
Click F6 to switch between the upper and lower panes
Scroll bar Set field properties in the lower pane
Primary Key – a field that identifies each record as being unique Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
9
Forms, Queries, and Reports
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
10
Forms Form First record from table visible in form
Report
Query
Underlying table
Form
Forms, queries, and reports are all based upon data contained in a table Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
11
Forms allow us to create an interface that can be more user friendly and attractive than Datasheet View Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
12
2
Queries Criterion restricting dataset to show records that have a job title of Sale Representative
Backing-up and Renaming Access Files
Save As – different in Access than other Office applications
Query results showing only employees who are Sales Representative
Queries allow us to question data The answer to the query is a dataset The question asked is formed using criteria – the rules or norm that is the basis for making judgments Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
To save a database with a new name you must either:
13
Backing-up a Database
Save As saves only the current object, not the entire database
Backup the database Copy, paste, and rename the database
14
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Compact and Repair Compact and Repair is located under the Manage menu
Default file name of a backup file is the name of the database and the current date
Backing-up an Access file will produce a copy of your file with a default file name Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Fixes problems due to inefficient file storage and growth of a database
15
Filters
Should be performed everyday Often decreases the file size by 50% or more 16
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Filter by Selection
Create a subset of records Do not change underlying table data Two types
Table before filter by selection
Filter by Selection Filter by Form
Results of filter
Filter by selection being applied from pre-determined criteria
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
17
Selects only the records that match preselected criteria Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
18
3
Filter By Form
Applying and Removing a Filter Inequity setting used in a Filter by Form process
Toggle Filter icon
Filter icon in the Sort and Filter group
Selection of criteria during Filter by form process
Allows the user to select criteria with which to filter by Allows the specification of relationships in the criteria
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Once a filter is applied, the Toggle Filter icon will be available The Toggle Filter icon can be used to apply and remove the current filter as many times as desired
19
Sorting Table Data
Access or Excel? Use Excel when:
Last Name field sorted ascending
Last Name field sorted descending
Lists records in ascending or design order according to one or more fields Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Use Access when:
You are working with large amounts of data You need to create relationships between your data You rely on external databases to analyze data 22
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Using the Relationship Window
Relational database This is much more management systems efficient than the allow data to be opposite of an RDBMS grouped into tables which is a flat file. Flat and relationships files store data in one created between the single file with no tables special groupings or collections
Relationship window
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
Your data is of a manageable data size There is no need for relationships between data You are primarily creating calculations and statistics
21
Relational Database - RDBMS
20
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
23
Show Table dialog box
Add the tables or queries from the Show table dialog box Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
24
4
Establishing Relationships
Establishing Relationships
Click and drag to create a relationship
Primary Key Foreign Key
Infinity symbol notes referential integrity has been applied
In the Relationship window, click and drag a field name from one table to a field name in a related table Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
25
Referential Integrity
Enter the appropriate settings in the Edit relationships dialog box Click Create A join line will appear when one table is joined to another Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
26
Questions?
Enforce Referential Integrity
Referential integrity ensures that the data in a relational database maintains consistency when the data are updated Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
27
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
28
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.
Copyright © 2010 Pearson Education, Inc. Publishing as Prentice Hall
5