Transcript AccessOverview

Microsoft Access Overview
Lydia Scheer, ITEP
1
The Database Environment
• Access is a relational database
– Data stored with Access are related to one another in
multiple ways
– Access maintains the data relationships when
database is structured correctly
• MS Excel (or other spreadsheets) are
considered “flat” file databases
– Rows and Columns of data that may be related
– Excel has no way of maintaining relationships
automatically
2
How do Relationships Work?
A relationship works by matching data
between tables based on values in key
fields. In most cases, these matching
fields are the primary key from one
table, which provides a unique
identifier for each record, and a
foreign key from another table.
3
4
Relationships Window
• View, define, and modify relationships
• Tools (Menu)Relationships… or right-click on
database window (white area) to view
• Join lines indicate type of relationship
(1=one-side;
= many side)
• Double-click on a join line to modify
relationship
• Tables/field lists are moveable/sizeable
• A table can be related to multiple tables, but
only by ONE field at a time
5
Types of Relationships
• One-to-One
– Item (record) in Table A related to only one item in Table B
– Not common because usually the information is stored all in one table
– Example: Data archive table; Subset of information that would result
in too many blank fields in main table (confidential info, instructors,
etc.)
• One-to-Many or Many-to-One
– One item in Table A can be related to multiple items in Table B
– Example: One site has multiple (many) monitors; One person has
multiple visits to the clinic
• Many-to-Many
– Combination of two One-to-Many relationships
– Example: One person has multiple visits to the clinic; One clinic has
multiple people visit.
– Some Many-to-Many relationships are not immediately obvious
(Example: One site has multiple monitors, but does a monitor have
multiple sites?)
6
Join Lines (Identify
Relationships
7
The Structure of Access
•
Database File: This is your main file that encompasses the entire
database and that is saved to your hard-drive or floppy disk.
Example: DMExample.mdb
•
Table:A table is a collection of data about a specific topic. There
can be multiple tables in a database.
Example #1: tblFIPSCodes
Example #2: tblAQS_Ozone
•
Field:Fields are the different categories within a Table. Tables
usually contain multiple fields.
Example #1: FilterID
Example #2: Date
•
Datatypes:Datatypes are the properties of each field. A field only
has 1 datatype.
FieldName: FilterID
Datatype: Number
•
Value: The Data.
Example (FilterID): 7544231
8
The Database Window
•Default view when database is opened
•Lists all available database objects
–Tables, Queries, Forms, Reports, Pages, Macros, Modules
•Lists all available database object groups
–A way of organizing shortcuts to objects that are related
–Create groups specific to your project/needs (e.g.
“Favorites”)
•Toolbar across top has commands for database
objects
–Open, Design, New, Delete
–View style (4 buttons on the left)
9
The Database
Window
10
Object Views
•Datasheet View
–Tables, Queries, Forms
–Standard table view – “spreadsheet style”
•Form/Page View
–Standard form view or page view
•Print Preview
–Standard view for Reports
–Can also be used with Forms, Tables, Queries & Pages
•Design View
–Applicable to all database objects
–Layout/view is specific to the database object
–Make changes to the structure/format/layout of object
11
Database Objects-Tables
• Used for storing data in rows and columns
• Rows = Records; Columns = Fields
• Records are UNIQUE collections of data about
“something” (e.g. a person, a PM filter, etc.)
• Fields are categories of data for a set of records
• Tables contain information about one subject only
• Primary Key field in a table is a unique identifier
for each record—no null values or duplicates
• Tables can have up to 255 fields, and can store up
to 1 gigabyte (GB) of data per table
12
Design View
Datasheet View
13
Database Objects-Queries
• Retrieve sets of data based on user-defined
criteria (text strings, expressions, functions)
• Often used to combine data from multiple (usually
related) tables
• Subsets of data stored in tables; data are not
actually stored in queries
• Perform calculations on or with data
• Can be constructed to perform an “action”
– create a new table from query results
– append records to an existing table
– update records in an existing table (similar to
find and replace, but can use calculations, too)
14
QBE Pane or
Query Design
View
15
Append Query Grid:
Update Query Grid:
Make Table or Append Query Pop-up:
Delete Query Grid:
16
Database Objects-Forms
• User-friendly way of displaying data for entry or
manipulation
• Programmed actions (macros, modules) can be
attached to forms
• Display data from tables and/or query results
• Can also be used as a menu (or switchboard),
created as custom dialog boxes, or displayed as a
datasheet
• Subforms-form within a form that displays data
from a different dataset than that of the main
form
17
Data Entry Form
(with Tabs)
“Switchboard”
Form
18
Database Objects-Reports
• Output of information from database (tables or
queries) in the form of a printed report
• Allows you to group and summarize information
• Can be previewed on the screen prior to printing
• Can include graphs, data tables, images, and
calculations/totals
• Reports are for publishing data only, not for data
manipulation
19
20
Database ObjectsMacros & Modules
• Macros
– Access-specific “mini-programs”
– Pre-programmed for ease of use
• Modules
– Visual Basic for Applications (VBA) programming language
– More complicated to use properly, but more options and
flexibility than macros
• Both are based on event-driven programming
– An event (click a button, open/close a form, etc.) triggers an
action (macro/module) that carries out a programmed set of
commands
– Most actions have required arguments (user-specified data that
tells the event how/when to carry out the action)
– Must act on a certain object or control on the form or report
• Can only be attached to forms or reports
21
22
Adding a Macro or Module (Event
Procedure) to a Form Event
23
Wizards
• Access has a number of wizards built
in to make it easier to work with
each tool
• Wizards walk you through creating db
objects
– Tables & queries
– Forms & reports
– Other items inside above (expressions,
events, etc.)
24
Simple Query Wizard asks for a table or query to
use for base data
Then you have the option of displaying all
records/fields or creating a summary query
Final query results
(grouped by Month
as an option)
displays Avg, Min,
and Max of monthly
PM mass
25
Things to make your life
easier…
• Naming Conventions
– Consistency in naming different types of objects, controls, etc.
– Use identifiers in names (e.g. tbl, qry, frm) that make sense to
you
– NO SPACES or Punctuation in names-harder to deal with in
expressions and code procedures
– Examples: tblSites, qryAQS_OzoneArizona, cmdClose,
dap2003SummaryDataPMFineHighestConc
• Document what you are doing…will you remember
what you did (or why) in three months time?
• Changes made to data are PERMANENT
• Backup….BACKUP…BACKUP!
26
The Database Window Toolbar
View Object in
Design View
Open Current
Object
Delete Current
Object
Create a
New Object
View Objects
as Small Icons
View Objects
as Large Icons
27
View Objects
with Details
View Objects
as a List
The Database Toolbar (Default)
Paste or
Clipboard Tool
Save
Print
Preview
Cut
Undo Action
Create a new
Database File
Show Object
Properties
Analyzer Tool
Format Painter
VBA (Code) Window
View, Edit, or
Create Indexes
Help Files
Spell Checker
Open
another
database file
Copy
Publish or Merge with
another Office Application
Print
28
View
Relationships
Table (Datasheet View) Toolbar
Switch to
Table
Design View
Save
Table
Design
Print
Table
Table Print
Preview
Insert Hyperlink ( to
Internet or other
network location)
Sort Field
Data by
Ascending
Values
Sort Field
Data by
Descending
Values
Filter by
Selection
29
Filter
by
Form
Apply/
Remove
Filter
Find a
Value
Delete
Record(s)
Add/Go To
New Record
New Object
(incl.
AutoForm &
AutoReport)
Database
Window
Table Design View Toolbar
(also for Query and Form Views)
Switch to
Table
Datasheet
View
Print Table
Design View
Save Table/
Query/Form Design
Set Field(s) as
Primary Key
View/Set
Indexes
30
Insert a
Row
(Field)
Delete a
Row
(Field)
Table
Properties
Field
Builder
Wizard
Query Design View Toolbar
Switch to
Query
Datasheet
View or
SQL View
Save
Query
Print Query
Design View
Choose or Change
Query Type (delete,
append, select, etc.)
Undo Last
Action (only
allowed once)
Add a table to
the QBE Pane
Run Query (select
queries display in
datasheet view;
action queries
attempt to run)
31
Show Top Values
(all, 25, 5%,
etc.)
Create a
Totals Query
Expression
Builder or
Calculated Field
Wizard
Query
Properties
New Object
(incl.
AutoForm &
AutoReport)
Form Design View Toolbar
Switch to
Form Design
or Datasheet
View
Identifies the
control on the form
which has the focus
Font Selection Box for
controls with dynamic
fonts (text boxes, labels,
buttons, etc.)
Field List-shows
available fields for
use on the form
Toolbox for
Form Controls
Font Size
Indicator
Format Font (Bold,
Italicize, Underline)
32
Open VBA
Window
AutoFormat
Form Design
Expression
Builder
Wizard
Text Alignment:
Left, Center, Right
Properties for
selected
control(s)
Fill Color, Font
Color, Line Color
Form/Report Design Toolbox
Select Control ToolUse to select a
control on a form
or report to
modify, delete,
move, etc.
Control Wizard—Selected means wizards will be
activated when a tool is chosen to help the user
establish the control properly. Deselected
means wizards will not be activated when a tool
is chosen, and enables the user to design a
control with his/her own methods.
Textbox Tool
Create an Option
Group
Label (control) Tool
Toggle Button Tool
Option (radio)
button; Check box
Create Combo Box
Create a List Box
Command button
Create an Unbound
Object Frame
Insert Page Break
Create a Bound
Object Frame
Insert Image or
Graphic
Line Tool
Tab Control
Rectangle Tool
Click to view more
controls for use on
forms/reports
Insert a Subform/
Subreport
33
Report Design View Toolbar
Save Report
Design
Switch between Print
Preview, Design View
and Layout Preview
Print
Report
Print
Preview
Insert
Hyperlink
Show
Field LIst
AutoFormat
Report
Layout
Properties
for selected
control(s)
VBA
Window
Format
Painter
Show
Toolbox
34
Sorting/
Grouping
Options
Expression
Builder Wizard
Macro Design View Toolbar
Save Macro
Print Preview Macro
Definitions (summary
of macro actions and
arguments
Show/Hide Macro
Names Column
Undo last action
(can only use
once)
Insert a
Row
Show/Hide Macro
Conditions Column
35
Delete a
Row
Run
Macro
Step Through
Macro Actions
Expression
Builder Wizard