Transcript Introduction to Databases - Pioneer Student

Microsoft Access
Introduction – Tables and Forms
©Richard Goldman
January 2000
1
What is a database?
• A data base is a table of data arranged for
convenient access.
• A relational database is a collection of
related tables.
• With Microsoft Access you can create
relational databases.
Page 2 ©Richard L. Goldman
What is a Database Management
Systems?
• A computer programs that allow you to create
databases and manipulate the data.
• Microsoft Access is Database Management
System.
• Other popular database programs:
–
–
–
–
–
–
Microsoft Works & Claris Works – home use
FileMaker Pro – Mac/Windows
Microsoft FoxPro – developers
Microsoft SQL Server – large organizations
Oracle – large organizations
dbase IV – early db – still around
Page 3 ©Richard L. Goldman
What is a Table?
• A table contains a group of related fields.
• The contents of each field is called a field value.
• A set of field values is called a record.
Fields
Field Value
Record
Name
HairColor
Phone
Age
Bob Smith
Black
456-9876
42
Jane Smith
Blonde
456-9876
39
456-9877
13
Mary Smith Red
Page 4 ©Richard L. Goldman
How are Tables Related?
•Tables can be related to each other if there is
a common field in each table.
Order Table
Name
OrderNumb Date
Cost
Bob Smith
5878
2-2-2002
250.00
Bob Smith
4357
4-1-2000
39.00
Mary Smith 9984
2-9-2000
5.13
Address Table
Name
Street
City
State
Bob Smith
123 Adams
Salem
OR
Jane Smith
55 Oak
Salem
OR
Boise
ID
Mary Smith 472 Circle
Page 5 ©Richard L. Goldman
What are the Rules for Relating
Tables?
One table must have a field with unique data in each record.
This field is called the Primary Key.
The matching field in the other table is called the Foreign Key.
Name
Street
City
State Address Table
Bob Smith
123 Adams
Salem
OR
Jane Smith
55 Oak
Salem
OR
Boise
ID
Mary Smith 472 Circle
Order Table
Name
OrderNumb Date
Cost
Bob Smith
5878
2-2-2002
250.00
Bob Smith
4357
4-1-2000
39.00
Mary Smith 9984
2-9-2000
5.13
Page 6 ©Richard L. Goldman
What is in an Access Database?
•
Access is built around six main objects groups
that are available from the Database Window:
1. Tables – Contains all your data
2. Queries – Programs that you create to find specific
data by filtering or sorting.
3. Forms – Screen that you create to display or input
your data.
4. Reports – Pages that you create to print out your
data.
5. Macros – Small programs that you can create to
perform routine tasks.
6. Modules – Programs that can be written to perform
simple or very complex tasks.
Page 7 ©Richard L. Goldman
What is in each Object Group?
•Most Object Groups that we work with will
have a Design View and a Datasheet View.
• The Design View allows
you create and edit the
object you are working
with.
• The Datasheet View
allows you to see and edit
the data you are working
with in a table format.
Page 8 ©Richard L. Goldman
How do you navigate through
the records of a table?
In addition to the standard horizontal and vertical scroll
bars, Access has special record navigation buttons.
You can also type the number of the record that you want
into the Record Number box.
First
Previous
Next
Last
New Record
Page 9 ©Richard L. Goldman
How do you know which record
is selected in a table?
• The Current Record Symbol identifies the
currently selected record.
Page 10 ©Richard L. Goldman
What is a Query?
• A query is a set of instructions that you
give Access that tells it which records and
field that you want to display.
Page 11 ©Richard L. Goldman
How do you create a Query?
• You can use the Query Wizard which
guides you through the steps of creating
one.
• Or, you can design your own.
• You can create a query based on one or
more tables and or other queries.
• The query will be sorted on the Primary
Key unless told otherwise.
Page 12 ©Richard L. Goldman
Creating a Simple Query using
the Query Wizard
• Select the Queries group from the Database
Window.
• Select the New button.
• Select Simple Query Wizard and then OK.
• Select the table or query to base the new query on
from the Tables/Queries box.
• Select the fields that you want from the Available
Fields box and click the > button to add them.
• Click the Finish button after you have selected all
the fields that you want.
Page 13 ©Richard L. Goldman
How do you sort a table or
datasheet?
• Click anywhere in the column that you
want to base the sort on.
• Click either the ascending or descending
button.
Page 14 ©Richard L. Goldman
How do you print a table or
datasheet?
• Click the Print button on the toolbar.
• Or, select the File menu and then select Print.
• Note: If it is a large table or data sheet it will be
broken up and printed on several sheets.
• We will be learning to use Queries and Reports to
allow us to print just the data that we want in an
easier to read format.
Page 15 ©Richard L. Goldman
What is a form?
• A form is custom screen display of your
data.
• You can create one with as much or as little
of your data as you wish.
• You can allow users to enter, edit, or just
view whatever data you wish.
Page 16 ©Richard L. Goldman
How do you create a Form?
• You can use the Form Wizard which
guides you through the steps of creating
one.
• You can use the AutoForm: wizards.
• Or, you can design your own.
• You can create a form based on one or
more tables and or other queries.
Page 17 ©Richard L. Goldman
What does the AutoForm: Columnar
wizard look like?
• The size of the Field Value box represents the
size of the of the field.
Page 18 ©Richard L. Goldman
What does the AutoForm: Tabular
wizard look like?
Page 19 ©Richard L. Goldman
What does the AutoForm: Datasheet
wizard look like?
Page 20 ©Richard L. Goldman
What does the AutoForm: Justified
wizard look like?
Page 21 ©Richard L. Goldman
Can you print a form?
• Yes, just select the File menu and then
select print.
• You may print the selected record, a range
of records, or all records.
• You may also print by clicking on the print
button on the toolbar if you want to print
all the records.
Page 22 ©Richard L. Goldman
How would you print just
records 7 through 11?
Select the File menu, then select Print.
Click the Pages radio button.
Type in 7 in the From: box.
Type in 11 in the To: box.
Click OK.
Page 23 ©Richard L. Goldman
What are the features of Help in
Access?
• Access Help provides the same features as
the Help system in other Windows
programs.
– Contents
– Index
– Find
Page 24 ©Richard L. Goldman
How do you use the Office
Assistant?
• Click the Office Assistant button on any
toolbar.
• Type a question in the text box.
• Click the search button.
• Choose a topic from the list displayed.
Page 25 ©Richard L. Goldman
What is a Report?
• A formatted printout of data selected from one or
more tables, queries, or other reports.
• Reports allow you to group and present data on
paper in a variety of ways.
• You may the create report yourself or use the
Report Wizards.
• After creating a report with a wizard, it will be
displayed in Print Preview.
Page 26 ©Richard L. Goldman
What does AutoReport: Columnar look like?
Page 27 ©Richard L. Goldman
What does AutoReport: Tabular look like?
Page 28 ©Richard L. Goldman
What does AutoReport: Justified look like?
Page 29 ©Richard L. Goldman
End
Page 30 ©Richard L. Goldman