Transcript Access tutorial 1

Microsoft Access – Tutorial 1
if you need to review general Microsoft Office procedures
such as:
 menus
 toolbars
 task panes
 files
 help
 printing
 please go through the first section of your text book

Microsoft Access – Tutorial 1
Access Tutorial Files



Tutorial 1 uses Northeast Seasonal Jobs International (NSJI)
this organization is a placement agency that helps foreign students to get
seasonal work (summer jobs) in the U.S. and Canada
NSJI uses Microsoft Access to join students and to job postings
Microsoft Access – Tutorial 1
Introduction
 databases are used to:
 organize
 store
 maintain
 retrieve
 sort
 information such as:
 employer’s names and addresses
 available positions and wages
Microsoft Access – Tutorial 1
Introduction
 data must be organized into fields
 fields are a single characteristic or attribute of a:







person
place
object
event
idea
examples of fields include:
 employer ID
 employer name
 employer address
 employer phone number
much as you would see in a contact list
Microsoft Access – Tutorial 1
Introduction
 related fields are grouped together into tables
 a table is a collection of fields that describe a person, place,
object, event or idea
 the field value is the specific content or value of a field and
is listed at the top of the table column
 the values that appear below the field values in rows are
called records
 a collection of related tables is called a database or
relational database
Microsoft Access – Tutorial 1
Introduction
 open the database file seasonal in the folder
COMP1115TedSnell on the shared/common directory
 the Database window will open
 on the left of it, you will see the Objects bar
 from the objects bar, you can view and work with the major
object groups such as:




tables
queries
forms
reports
Microsoft Access – Tutorial 1
Tables
 you will notice that there are two tables, Employer and
NAICS
 NAICS contains North American Industry Classification
Codes which classify businesses according to their activities
 open the Employer table and maximize it
 the table opens in datasheet view and looks much like an
Excel spreadsheet
 this table has 13 fields and 45 records (scroll to see them)
 you can select fields by clicking on the column headings
 you can select records by clicking on the record selector
(left side of the row)
 you can navigate through records using the navigation
buttons on the bottom left
Microsoft Access – Tutorial 1
Keys
 you will notice the Employer ID field on the left side of the
table
 it is referred to as the primary key of this table
 a primary key is a field (or collection of fields) whose values
uniquely identify each record in a table
 the primary key is unique and never repeats in the table
Microsoft Access – Tutorial 1
Keys
 tables are connected together using common fields
 common fields appear in more than one table
when the primary key from one table appears in another
table, it is called a foreign key
 foreign keys may be repeated
 note the NAICS field in the Employer Table

Microsoft Access – Tutorial 1
(relational) database management systems (DBMS)
 a database management system, such as Microsoft Access
is used to manage databases
 you can:

create database structures containing









fields
tables
table relationships
add new records
change field values in existing records
delete records
build queries to answer questions about your data
create reports
protect databases through security, control and recovery
Microsoft Access – Tutorial 1
Queries
 a query is a question you ask about the data stored in your
database
 Access responds to your query by displaying the specific
records and fields that answer your question
 create a query by telling Access:


which fields you need
what criteria should be used to select the records

Access displays only the information you want

ex. display employers located in Boston
Microsoft Access – Tutorial 1
Queries
 close the seasonal table and click queries in the Objects bar
 open the query called Contacts
 the query displays the Employer information in a different
way
 it does not affect the original table
 create a query using the Query Wizard
Microsoft Access – Tutorial 1
Queries
 Create tab query wizard
 select the Simple Query Wizard and click okay
 select the Employer table from the Tables/Queries list
 include the following in the query:
 EmployerName, City, StateProv, ContactFirstName,
ContactLastName, Phone
 click next
 name the query EmployerList and finish
 sort the query list by clicking the arrows beside the column
names (ex. by name ascending)
 note that you can also filter
 try filtering the list for employers in Halifax
Microsoft Access – Tutorial 1
Forms
 forms allow you to maintain, view and print records
 click Forms in the Object bar
 Create (tab)> More Forms > Form Wizard to open the New
Form Dialog box
 select the query Employer List that we created earlier, then
click next
 choose Columnar for the layout
 Give the form a style such as Metro, then click next
 try modifying the form’s design
 you can edit information in this view
 navigate your records with the buttons on the bottom
 new records can be created using the >* button
Microsoft Access – Tutorial 1
Forms
 go back to the Form view on the top left
 you can edit information in this view
 try editing a record in this view
 navigate your records with the buttons on the bottom
 new records can be created using the >* button
 try creating a new record
 close the form and look at the original table
Microsoft Access – Tutorial 1
Reports
 a report is a formatted printout (or screen display) of the
contents of one or more tables
 reports can be easily created using the Report Wizard
 go to the Reports section of the Objects bar
 create a New report with the Report Wizard (Create >
Report Wizard)
 Add all the fields from the Employer List Query
 Make a columnnar report with the Aspect Theme
 you can navigate your records using the buttons on the
bottom