Transcript Practical 1-Tables

Nos 215
Database Systems
Microsoft Access Practical #1
Creating Tables
Microsoft Access
• For the first part of our practical database
management, we will focus on Microsoft Access
XP.
• We will cover how to create databases, create
tables, create forms, queries, reports,
PivotCharts, Access Webpages and Macros
• By the end, you should be familiar with Access
to create and manage your own database
(which you will do for your project)
Create Database and Tables
• To start, you want to go to File->New and a
menu on the side will appear
• This will let you choose Blank Database, Blank
Access Page and Project
• The Project is for when you will be connecting to
a Microsoft SQL server over a network. The
Access Page also requires a networked SQL
connection
• To just make a local database, we will choose
Blank Database. You can enter a name for your
database after that and choose where it will be
saved
Creating Tables
• The Database menu will come up and let you
choose what action you want to perform
• You can select Tables and this will let you create
tables, edit or delete them
• If you have a standard database that you are
creating (like a database of Customers),
choosing “Create table by using wizard” will be
the easiest to make a new database
• A screen will appear that lets you choose what
fields you want to have on your database
Create tables by wizard
• The wizard lets you select from a wide variety of different
categories that might be applied to different
circumstances
• Select the category, either personal or business, then
select the field you want to add to your new tables. You
can select all the fields in a category by clicking on the
“>>” button
• After you select your fields, press next, and you can
choose a primary key if you want and give the table a
new name
• Then you can choose if there will be relationships
between tables.
• Finally, you will be asked to enter information into the
database right away
Tables and words
• So in Access what we call Tables are also
known as “Entities”
• Fields in Access are also known as
“Attributes”
• Relationships are still called relationships
• The primary key is a field that is chosen to
be a special field and works the same way
as in theory
Primary Keys in Access
• The power of a relational database system such as
Microsoft Access comes from its ability to quickly find
and bring together information stored in separate tables
using queries, forms, and reports.
• In order to do this, each table should include a field or
set of fields that uniquely identifies each record stored in
the table.
• This information is called the primary key of the table.
Once you designate a primary key for a table, Access
will prevent any duplicate or Null values from being
entered in the primary key fields.
• There are three kinds of primary keys that can be
defined in Microsoft Access:
• AutoNumber, Single Key and Multiple Key
Autonumber
• An AutoNumber field can be set to automatically
enter a sequential number as each record is
added to the table.
• Designating such a field as the primary key for a
table is the simplest way to create a primary key.
• If you don't set a primary key before saving a
newly created table, Microsoft Access will ask if
you want it to create a primary key for you.
• If you answer Yes, Microsoft Access will create
an AutoNumber primary key.
Single valued primary key
• If you have a field that contains unique
values such as ID numbers or part
numbers, you can designate that field as
the primary key.
• You can specify a primary key for a field
that already contains data as long as that
field does not contain duplicate values or
Null values.
Multiple value primary keys
• In situations where you can't guarantee the
uniqueness of any single field, you may be able to
designate two or more fields as the primary key.
• The most common situation where this arises is in
the table used to relate two other tables in a manyto-many relationship.
• For example, an Order Details table can relate the
Orders and Products tables. Its primary key consists
of two fields: OrderID and ProductID. The Order
Details table can list many products and many
orders, but each product can only be listed once per
order, so combining the OrderID and ProductID
fields produces an appropriate primary key.
• Each product can be listed only once per order.
Create table in Design view
Create table in design view
• Click on field name, enter in the field (attribute)
that you need to put in
• Choose a field type: This will say what kind of
data is stored inside the field.
• You can choose from text, number, date/time,
currency, autonumber and so on
• In most cases, text will suffice
• Then on the bottom you can change more
attributes about the field, like..
Attributes about Fields
• Field size: put the maximum number of characters per
entry
• Format: Changes how the data will be displayed. You
can enter in custom formats, like changing the color of
something, put in [Blue], [Green]
• Input Mask: Changes the way you can enter data. For
example, you can make a phone number look a certain
way before being entered
• Caption: Provides a label for reports and forms. If this is
blank, then it will just say the field name
• Default value: A value that will be entered automatically
in a record, unless it is changed to be something else
Attributes for fields
• Validation Rule: Make the user enter data in a
certain way.
– Examples:
• <>0 ----- Entry must be not zero
• >1000 or Is Null ----- Entry must be blank or greater than a
1000
• Like “A????” ----- Entry must be 5 characters and begin with
A
• >= #1/1/04# And < #1/1/05# ----- Entry must be a date in the
year 2004
• Validation text: the message that appears when
someone tries to enter a bad format
Attributes for fields
• Required: Does the user need to enter this
data?
• Allow Zero-length: can the entry be NULL or
nothing in it?
• Indexed: If you do a lot of searching and sorting
on the database, you can enter an index, which
will store the data specially. The only problem is
that it might make updating stuff slower
• The Lookup Tab: If you click on “lookup” you
can change what kind of thing will be displayed
on forms for this field. Textbox, List Box, Combo
Box
Lookup fields
• If you choose your field to be a combo or
list box, then you also have to specify
some other things
• Row Source type: this will determine what
is shown in the box of things to choose on
the form
• Row Source: The table where the data will
come from that will go inside the box
Enter data into tables
• After you have made your table, you can close it
and save, or you can go to view and choose
“Datasheet” view. This will take you to a window
where you can enter data
• Vice versa, to go back to the designing a table
window, go to view and choose “Design view”
• In the datasheet view, you merely need to enter
the data you want. To go from one field to the
next, press tab.
• To go to a new entry, press return
Finding data in tables
•
•
•
•
Open the table, and then activate the field for
which you plan to enter a value.
On the Standard toolbar, click the Find button .
In Find and Replace dialog box, in the Find
What box, type what you are looking for.
In the Look in box, a field is automatically
entered. In the Match box, Whole Field is
automatically entered. Click Find Next.
Sorting data
• Access 2002 displays information in a table in
alphabetical or numerical order based on the primary
key.
• However, you can sort information so that it appears in
an order that works best for you.
• In Datasheet view, you can sort all of a table’s records in
ascending or descending order, but you cannot use both
sort orders on more than one field.
• To sort records in Datasheet view
• With the table open in Datasheet view, click the header
of the field to sort so that the whole column is selected.
• Note If you select multiple columns, Access sorts
records starting with the leftmost selected column.
• Or on the Datasheet toolbar, click the Sort Ascending
button or the Sort Descending button .
Filtering Data
• A filter is a set of criteria applied to data in order to
display a subset of the data or sort the data. In general,
you use a filter to temporarily view or edit records that
contain a specific item. The rest of the records are then
hidden from view.
• In Access 2002, you can filter records in three ways:
• Filter By Form. Use this option when you want to
choose the values you’re searching for from a list without
scrolling through all the records, or when you want to
specify multiple criteria at once.
• Filter By Selection. Use this option when you can
easily find and select the value you want the filtered
records to contain.
• Advanced Filter/Sort. Use this option to search for
records that meet multiple criteria, search for records
that meet one criterion or another criterion, or enter
expressions as criteria.
Filter by form
•
•
•
•
With the table open in Datasheet view, on the
toolbar, click the Filter By Form button to
switch to the Filter by Form window.
Click the field in which you want to specify the
criterion that records must meet to be included
in the filtered set of records, and then in the
pull-down list that appears, click the criterion.
On the toolbar, click the Apply Filter button .
When you want to view the full table again, on
the toolbar, click the Remove Filter button.
Filter by selection
•
•
•
With the table open in Datasheet view,
click the item that you want to use to filter
data.
On the toolbar, click the Filter By
Selection button .
When you want to view the full table
again, on the toolbar, click the Remove
Filter button.
Advanced Filter
• Advanced filter lets you specify exactly what you
want to filter out based on specific criteria
• When you open Advanced filter you will see the
list of tables and fields. For each field that you
want to filter by, you can drag it to the bottom,
where the filters are created
• Then on the bottom, you can tell how you want
the sort order to appear and the criteria
• The criteria field uses expression builder to
determine what it will do
Expression Builder
• Expression builder is a tool that Microsoft has made that
will let advanced users do whatever they really need to
do, based on simple programming ideas
• If you right click on criteria, you can go to “Build” and be
taken to the Expression Builder.
• You will see a confusing screen. We will focus on simple
things the criteria builder can do.
• First select something you want to filter on, by opening
up a folder for a table and selecting a field. A bunch of
text will appear in the window where the expression is
built
• Now you can tell this field to behave in some way, such
as make sure that this equals “Hello” or make sure it is
not null. The range of things you can do here is too much
to cover in entirety
Expression Builder
• So to make something filter by being equal to
something else you can put in an equal sign and
then either type in the value you want it to equal,
or select another field you want it to equal to.
• You can also add in Or and And and Not
statements to chain together different filters and
match many things at once.
• Then press “OK” and apply filter. If nothing
comes up, then your filter is not correct and it
couldn’t match anything
Creating relationships
• You can also use Access to outline your
relationships between tables.
• Click on the “Tools->Relationships” menu item.
The relationships editor will appear
• You can add in tables here from your database
with a right click and “Show table”
• The table you chose will appear and you can
now create a relationship between two or more
tables by highlighting a field and dragging it to
another field in another table.
• A window will appear asking about the
relationship that you have just created
Creating relationships
• The window that appears will show you the fields that will
be connected through a relationship.
• Referential integrity is a system of rules that Microsoft
Access uses to ensure that relationships between
records in related tables are valid, and that you don't
accidentally delete or change related data.
• If you also click on cascading, Access will delete and
update data in related tables when changes are made in
another table.
• Press “Create” and you will see a line (relationship) has
been created between the two entities.
• This is an important step before you can create Queries
and form and reports that draw from multiple tables.
Summary
• The first section on Microsoft Access practical is
trying to ensure that you can create tables and
enter data efficiently.
• You should also be able to sort, and filter data
using different methods
• You should at least understand how to make
relationships in the relationship builder. If for
nothing else, this is good way to have the
computer draw the ER diagrams for you