Transcript Form one

Databases
Databases Using Microsoft Access 2003
Microsoft Access 2003 is a powerful, yet easy
to learn, relational database application for
Microsoft Windows. This Chapter is designed for
users who are new or only have little experience
using Access 2003. It introduces fundamental
database concepts and operations and illustrates
how they are performed in Microsoft Access
2003. This Chapter does not cover all of the
features and functions of Microsoft Access 2003,
emphasis will be on basic and frequently-used
features, such as the creation of tables and
queries, forms and reports.
Definitions
These words are used often in Access so you will want to become
familiar with them before using the program and this Chapter.
• Table: tables are the main units of data storage in a database. A
table is a collection of data about a specific topic; it is made up of
one of more fields.
• Field: a field is a column in a table and defines a data type for a
set of values in a table. For example, a mailing list table might
include fields for first name, last name, address, city, state, zip
code, and telephone number.
• Record: a record in a row in a table and is a set of values defined
by fields. In a mailing list table, each record would contain the data
for one person as specified by the intersecting fields.
• Data type: data types are the properties of each field. A field only
has one data type, such as Character, Number or Date.
• Primary Key: a primary key is a value that can be used to identify
a unique record in a table.
• Design View: it provides the tools for creating fields in a table.
• Datasheet View: it allows you to update, edit, and delete in
formation from a table.
Starting Access and Opening a Database
Click on the START button on your computer and position your cursor over the
PROGRAMS menu to view a list of installed programs. Once you see the
list, position the cursor over Microsoft Office and then click on Microsoft
Access.
If you want to edit an existing database, you can either go to File—Open, or move your
cursor to the lower right-hand side to click on Open.
If you are creating a new database, you can also either go to File—New, or move your
cursor to the lower right-hand side to click on Create a new file.
Unlike Word documents, Excel spreadsheets, and Power Point
presentations, you must save an Access database before you
start working on it. After selecting "Blank database", you will
first be prompted to specify a location and name for the
database. In the New Database dialog box, type in a name for
the database and a location and click on Create
Now a new database is created
•
•
•
•
•
•
•
•
Database Components
An Access database consists of several different components. Each component listed is
called an object.
Listed below are the names and descriptions of the different objects you can use in Access.
This Chapter will focus on the first four objects: tables, queries, forms and reports.
Tables
Tables are where the actual data is defined and entered. Tables consist of
Records (rows) and fields (columns).
Queries
Queries are basically questions about the data in a database. A query consists of specifications
indicating which fields, records, and summaries you want to see from a database. Queries allow
you to extract data based on the criteria you define.
Forms
Forms are designed to ease the data entry process. For example, you can create a data entry
form that looks exactly like a paper form. People generally prefer to enter data into a welldesigned form, rather than a table.
Reports
When you want to print records from your database, design a report. Access even has a wizard
to help produce mailing labels.
Pages
A data access page is a special type of Web page designed for viewing and working with data
from the Internet or an intranet. This data is stored in a Microsoft Access database or a
Microsoft SQL Server database.
Macros
A macro is a set of one or more actions that each performs a particular operation, such as
opening a form or printing a report. Macros can help you automate common tasks. For
example, you can run a macro that prints a report when a user clicks a command button.
Modules
A module is a collection of Visual Basic for Applications declarations and procedures that are
stored together as a unit.
Building Database Tables
This section describes how to construct data tables, forms, reports, enter
and edit data, sort records, and find records that meet certain conditions.
Creating Tables in Microsoft Access
By clicking the Table tab on the left hand side, you will find Access
provides three ways to create a table for which there are icons in the
Database window.
•
Create Table in Design view will allow you to create the fields of the
table. It allows you to define the fields in the table before adding any data
to the datasheet. This is the most common way of creating a table and is
explained in detail below.
•
Create table by using wizard will step you through the creation of a
table.
•
Create table by entering data will give you a blank datasheet with
unlabelled columns that looks much like an Excel worksheet.
Creating Tables in Design View
Double clicking on Create table in Design view displays the Table Design
screen where you define fields for your table. The screen is divided into
two parts: a top pane for entering the field name, data type, and an option
description of the field and a bottom pane for specifying field properties.
Every table consists of fields. For each field, specify the name
of the field, the type of data, and any description needed to
determine what data the field contains. Pressing the Tab key
moves the cursor from one column to the next in the Table
Design screen. You can select the data type from the dropdown list in the Data Type column, Data type, Top Pane and
Bottom Pane
Primary Key
Each table in your database should have a “primary key.”
A primary key is a field that uniquely identifies each record in
the database. In a database of book collection, there might
be two books with the same author so author is not a good
primary key. However, every book has a unique “call
number”, so “call number” would be a good choice for a
primary key. To set the primary key for your table, highlight
the key field and choose Primary Key from the Edit menu.
When the primary key is set, you should find a little key icon next to the field name on
the left side. Note: To remove a primary key, simply repeat this procedure to toggle the
primary key off.
Saving a Table
The table must be saved. Pull down the File Menu to choose Save. A dialogue box
will pop up where the name of the new table should be specified. Access gives a
default name such as “Table1” or “Table2.” Simply type over this default name with the
name of the table. For this example, name the table “Book_Info”, then click OK.
At this point, the new table has been created and saved. Switch
back to the Access main screen by pulling down the File menu
and choosing Close. This will close the Design View for the table
and display the Access main screen. Notice that the new
Book_Info table appears below the Table tab.
Adding and Deleting Fields to Tables
Even the best planned database will sometimes require you to add or delete a field in
the Design View. To add a new field, select the row you want to insert above and choose
“Row” from the Insert menu. If you need to delete a field, select the row containing the
field and choose “Delete Row” from the Edit menu.
You can also move fields in the table design. Select the field you want to move by
clicking on its row. A black triangle will appear to indicate the field is selected. Then, drag
the field to the new location.
Entering and Editing Data
After you have defined fields in the table, you can enter data. Highlight the table; choose
Open from the database window menu bar. This displays a grid with field names across
the top and a row for each record of the database.
To enter data, simply click the mouse in the field you want to enter and type a value.
Press the Tab key to move from field to field. When you are in the last field of a record,
pressing the Tab key automatically creates a new record for you.
Use the Datasheet View to add, delete, and move records.
To save the new data, pull down the File menu and choose Save.
Navigating Records
To navigate to other records in the table, use the navigation bar at the bottom of the
screen:
Sorting and Finding Records
Two of the most common database tasks are sorting records and searching for
records that meet a particular condition. Fortunately, both of these operations are easy
to perform while viewing the table datasheet.
Sorting Records
To sort records by a particular field, select the field you want to sort. From the Records menu
select Quick Sort then choose either Ascending or Descending order. Access quickly sorts the
records by the selected field.
Finding Records
If you want to locate a record that contains a certain name or date use the Find command on
the Edit menu. Type the search string in the Find What box and click Find First. Access will
highlight the first record that contains the search string.
• Table Relationships
To prevent the duplication of information in a
database by repeating fields in more than one
table, table relationships can be established to
link fields of tables together.
Follow the steps below to set up a relational
database:
1. Click the Relationships button on the toolbar:
2. From the Show Table window (click the Show
Table button on the toolbar to make it appear),
double click on the names of the tables you
would like to include in the relationships. When
you have finished adding tables, click Close.
To link fields in two different tables, click and drag a field from one table to the
corresponding field on the other table and release the mouse button. The Edit
Relationships window will appear. From this window, select different fields if
necessary and select an option from Enforce Referential Integrity if
necessary. These options give Access permission to automatically make
changes to referential tables if key records in one of the tables are deleted.
Check the Enforce Referential Integrity box to ensure that the relationships
are valid and that the data is not accidentally deleted when data is added,
edited, or deleted. Click Create to create the link
A line now connects the two fields in the Relationships window.
Note the symbols "1" (indicating the "One" side) and the infinity
symbol (indicating the "Many" side) on the relationship. Close the
relationships screen and select Yes to save the changes to the
Relationships layout.
Creating Queries
A query is simply a question you ask a database. How
many books are written by a certain author? Who wrote a
book in a certain year? Queries select records from one or
more tables in a database that match the criteria you set, they
can be viewed, analyzed, and sorted on a common datasheet.
The resulting collection of records, called a dynaset (short for
dynamic subset), is saved as a database object and can
therefore be easily used in the future. The query will be
updated whenever the original tables are updated.
To construct a query, Click on the New button in the
database window then Choose Design View, click OK.
In the Show Table dialog box you will be asked to choose table/tables for the query.
Select the tables you want to query and click Add.
Add fields from the tables to the new query by double-clicking the field name in
the table boxes or selecting the field from the Field and Table drop-down
menus on the query form. Specify sort orders if necessary.
• After you have selected all of the fields and tables, click the
Run button on the toolbar to execute the query records that
match the criteria you set.
Choose Save from the File menu to save a query for later
execution
• Creating Forms in Microsoft Access
•
Forms generally serve to define screens with which to edit the
records of a table or query.
• In this unit we will see how to create a form, and how to operate
it for the editing of records and changing its design.
• To create a form we need to position ourselves in the database
window with the Form object selected, if we then click on the
button a window opens with the various ways we have to
create a form:
•
•
•
Design view opens a blank form in design
view, and we then need to incorporate the
various objects that we would like to appear in
it. We will see ahead in this unit how to modify
the form design.
Form wizard uses a wizard that guides us
step by step in the creation of the form.
Autoform consists of automatically creating a
new form that contains all the data from the
source table.
• According to the type of form that we select (columnar,
tabular,...) the form will present the data in a distinct
way, when we click on one of the options, a sample
will appear on the left side with the way in which the
data will be presented with this option. E.g Autoform:
columnar Present’s one record on a screen,
meanwhile Autoform: tabular presents all the records
on one screen and every record in a row.
The Form's wizard
To start the wizard we can do it as describe in the last point or a faster way
would be from the Database window with the Forms object selected, by double
clicking on the Create form using wizard option.
The first window of the wizard appears:
• Then we are asked to introduce the fields to include in
the form.
• Firstly we select from the Table/Queries box the table
or query that we are going to get the data from, this
will be the form source. If we want to extract data
from various tables it would be better to first create a
query to obtain this data and then select this query as
the form source.
• Next we will select the fields to include in the form by
clicking on the field and then the button or simply
double click on the field.
• If we selected the wrong field click on the button and
the field will be removed from their selected fields list.
• We can select all the fields at the same time by
clicking on the button or deselect all the fields at
once using the
button.
• Next we click on the Next> button and the window
seen in the following example will appear...
Then select the data distribution
within the form. By selecting a format
it will appear on the left side the way
it will be seen in the form.
Once we have selected the
distribution of our choice click Next
and the following window will appear:
Then select the forms style, we can
select between the various defined
styles that Access has. By selecting
a style it will appear on the left side
as it will in the form.
Once we have selected a style of
our choice we click on the Next
button and the last screen of the
forms wizard will appear.
Then we are asked for the title
of the form, this title will also be
the name assigned to the form.
Before clicking on the Finish
button we can choose between:
Open the form to view or
enter information, in this case
we will see the result of the form
ready for the editing of data.
Modify the form's design, if
we select this option the
Form design view will
appear where we can modify
the aspect of the form, e.g:
• Creating Reports in Microsoft Access
• Reports are generally used to present the data of a
table or query in order to print them. The basic
difference with forms is that the data can only be
visualized or printed (it can not be edited) and the
information can be grouped and totals extracted by
group more easily.
• In this unit we will learn how to create a report using
the wizard, and how to change its design once
created.
• To create a form we need to position ourselves in the
Database window with the Reports object selected, if
we click on the button a dialog box will open with
the different types of reports that we can create.
Design view opens a blank report in design view and we then
need to incorporate the different controls that we want to appear
within it.
The Report wizard uses a wizard to guide us step by step
through the creation of the report.
Autoreport consists of automatically creating a new report that
contains all the data of the source table or query.
The Report wizard
To start the wizard we can use the method explained in the previous point or a faster and
easier method would be from the Database window with the Reports object selected to
double click on the Create report by using wizard option.
The wizard's first window will appear:
• Then we are asked to introduce the fields to be
included in the report.
• Firstly we select the table or query from the
Tables/Queries box where it should extract the data
from, this will be the report source. If we want to
extract data from various fields it would be best to
create a query to obtain the data and then to use this
query as the source of the report.
• Next we select the fields by clicking on the field and
then on the button, or simply double clicking on the
field.
• If we make a mistake we click on the button and the
field will be removed from the list of selected fields.
• We can select all the fields at the same time by
clicking on the button, or deselect all at the same time
by clicking on the button.
• Click on the Next> button and the next window will
appear...
Then select the grouping levels within the report. We can group the reports by
way of various concepts, and with each concept add a group header and a
footer, and in the group footer we will normally see the group total.
To add a grouping level click on the field by which we want to group and click
on the
button (or double click directly on the field).
A diagram will appear to the right indicating the structure that our report will take
on, and in the central zone the fields that are seen for every record will appear.
In our example a group by city will appear at the top and a group by postal code
will appear at the bottom.
To remove a grouping level click on the header corresponding the group and
If we want to change the order of the defined groups we use the
button, the upward arrow will move us up a group, and the downward arrow will
move us down a group.
To continue with the wizard we click on the Next> button and the
following window will appear.
Then select the type of data layout within
the report. By selecting a distribution the
aspect that the report will take with this
distribution will appear in the diagram to
the left.
In the Orientation section we can select
from either a Portrait or a landscape
printing (oblong).
With the Adjust the field width so all fields
fit on a page the wizard will generate the
fields in this way.
We then press the Next> button and the
following screen will appear:
Then select the type of style we would like
our report to have, we can select from the
various defined Access styles. By selecting a
style the aspect that this report will take with
this style will appear in the diagram to the left.
Once we have selected a style we click on
the Next button and the wizards last screen
will appear
Then we are asked the title of
the report which will also be
the name assigned to the
report.
Before clicking on the Finish
button we can choose>
Preview the report: in this
case we will see the result of
the report for the printing.
Modify the report's design:
if we select this option the
Form design window will
appear where we can modify
the aspect of the report.