Transcript Slide 1

Intro to Access 2007
Lindsey Brewer
CSSCR
September 18, 2009
What we will be learning today
•
•
•
•
•
•
•
•
•
Access vs. Excel
Starting with Access 2007
Create a database
Tables
Forms
Relationships between data
Queries
Reports
Where to find more information
When should I use Access vs. Excel?
• Excel is designed for flat data structures
▫ analysis of data is the primary goal
▫ Excel is designed to store numbers rather than text
• Access is designed for relational data structures
▫ relational data divides your data into different pieces
through different tables
▫ using separate tables can help make data easier to
manage
▫ storage of data is the primary goal
▫ Access can hold much more data than Excel and is
better at storing text
▫ Access is also a better choice when there are multiple
users working on one data file since Access only locks
a record when a user is changing it.
Access 2007 and older versions
• The file extension for Access 2003 is .mdb, and
Access 2007 is .accdb.
• To go between the different versions of Access,
you can save and upgrade files into different
formats with the “Save As” button.
Open Access 2007
• Click START
• Click ALL PROGRAMS
• Click MICROSOFT OFFICE
• Click MICROSOFT OFFICE ACCESS 2007
The getting started page
• This page has
categorized templates
that you can choose
from.
• If you are creating a
new template, you can
select a blank database
option and give it a
new name in the far
right pane.
• You can also download
an existing database.
Create a database
• Click on BLANK DATABASE at
the top of the getting started
page
• Give the file the name SURVEY
on the right
• Click CREATE
Tabs of Access 2007
• Home
• Create
• External Data
• Database Tools
• Datasheet
Tables – option 1
• Tables are places to store your raw data in a
spread-sheet like format
• There are two different ways to make
tables.
• First, you can select a table template which
is a table already set up for you by Access.
• Click on CREATE.
• In the TABLES group, click TABLE
TEMPLATE.
• This can be handy if you want to create a
table for the areas that Access has already
established.
Creating a table – option 2
• Click on the HOME
tab
• You can create a
more customized
table by selecting
DESIGN VIEW in
the upper left hand
corner.
• Give the Table the
name CONTACTS
Adding field names to a table
• The ID field name is
already filled in when
you create a table
• Enter
▫
▫
▫
▫
▫
▫
LAST NAME
FIRST NAME
EMAIL
PHONE
NOTES
MOST RECENT
CALL
▫ INTERVIEWER ID
• Change the data type
of for MOST
RECENT CALL to
DATE/TIME
Table creation continued
• Click VIEW in the upper
left hand corner and
switch to DATASHEET
VIEW
• Click YES when you are
prompted to save your
table
• Another way to save a
table is to right click on
the table’s tab
• Now we can enter data
into our new table
• After entering
information, right click
on the contacts tab and
click SAVE
Forms
• The form gives you a easy to use interface for
data entry and editing. Forms aren’t necessary,
but they can make data easier to manage.
• Click the CREATE tab
• Click FORM in the FORMS group
• When you are in LAYOUT VIEW, you can easily
delete parts of a form by right clicking and
selecting delete. This will not delete the field
name from your table.
• Right click and select SAVE.
Creating a new entry using forms
• To enter data into your table, you can use forms.
• Switch the form into FORM VIEW
• Click NEW (BLANK) RECORD at the bottom of the
form box
• Enter your new data in the form
• Save your form, and hit the REFRESH button to see
your data appear in your CONTACTS table
Creating split forms
• Split forms allow you to
view the datasheet and
the form at the same
time
• Click on the CREATE
tab
• In the FORMS group,
click SPLIT FORMS
Queries
• Queries enable you to specify the information
that you want to display.
• It is important to note that the query data is live.
That means that if you change the data in the
query results, it will be changed in the entire
database.
• Deleting data in a query deletes the data
everywhere!
Run a query
• Click on the CREATE tab
• Click QUERY DESIGN
• Select the table that we want to query. Select
CONTACTS.
• In CONTACTS, double-click the fields that you want to
see in the results of the query.
• Add fields LAST NAME and NOTES and MOST RECENT
CALL
• In the RESULTS section, click RUN!
Putting parameters on a query
•
•
•
•
•
•
•
•
•
Click on the CREATE tab
Click on QUERY DESIGN
Add table CONTACTS
Add LAST NAME, NOTES, and MOST RECENT CALL
In the CRITERIA row, enter the criteria that you would
like to put on the query.
Enter “Between Date() And Date()-6” under MOST
RECENT CALL
This will find all data for calls that have happened in the
past week
To find the syntax for other criteria, type in EXAMPLES
OF QUERY CRITERIA into the help menu.
Click RUN!
Create a report – option 1
• Reports are ways of presenting your data.
• Select the table or query that you would like to
be the basis of your report. In this case,
CONTACTS.
• Click the CREATE tab.
• In the REPORTS group, click REPORT.
• Access will create a report using the data from
CONTACTS.
Create a report – option 2
• Use the REPORT
WIZARD to create a
report
• Click the CREATE tab
• In the REPORTS
group, click REPORT
WIZARD
• Select the options that
you would like for
your report
Editing your report
• View your report in LAYOUT VIEW
• Use the commands in the FORMAT, ARRANGE,
OR PAGE SETUP tabs.
• View your report in DESIGN VIEW
• Change the titles and other design features.
Relationships
• Relationships bring tables together so you can
gather the information that you need.
• Relationships are made by including one field
from one table as a field in another table.
• Primary key – the field which is included in
another table to create a relationship. Each table
has a primary key.
• Foreign key – the name of the new field once
the primary key is shared with another table.
Three types of relationships
• One-to-one: A single record in one table is related
to a single record in another table, and vice versa.
• One-to-many: A single record in a table is related
to many records in another table. These are the most
common type of relationship in databases.
• Many-to-many: Several records in one table are
related to several records in another table.
More information
• To learn more about Access 2007, you can visit
Microsoft’s website
http://office.microsoft.com/enus/access/default.aspx. They have tutorials,
demos, and webcasts available.
• CSCCR has a handout about Access 2000 and
relational databases on its website.