LECTURE 4

MICROSOFT ACCESS
Database Basics
History
 Access is one of the few products originally developed by
Microsoft
 Development began in the mid 1980’s
 Combined with other databases that Microsoft licensed such
as R:Base and FoxPro
 Released in November 1992 as a single user application for
very small (<10mb) files
 Became dominant database for windows when competitors
failed to transition to Windows successfully.
 Now a very stable and robust application, scaling from 1 to
many users and up to 2g of data in each file
What is Microsoft Access?
Microsoft Access is a relational database management
system (RDBMS). At the very core, it is a software
“engine” that provides an interface between physical
data and user application queries.

Other examples of DBMS applications include:

•Oracle
•mySQL(Structured Query Language)
•SQL Server (Microsoft)
•DB2 (IBM)
•Informix
 There are two basic types of
databases:

1. Flat File: A flat-file database stores all of its information

names, addresses, etc. in the same place. Flat-file
databases are incredibly simple to create and use, but
they're not very powerful or well suited to many business
tasks.
2. Relational: A relational database contains multiple tables
that are related through matching fields.
 What is a Database?
 A structured collection of related data
 A filing cabinet, an address book, a
telephone directory, a timetable, etc.
 In Access, your Database is your
collection of related tables
What is a Relational Database?
 A relational database is a collection of tables
from which data can be accessed in many
different ways without having to reorganize
the database tables.
 That is, once relationships are created, tables
can “talk” to each other. We can link (relate) the
tables to find:
 Which doctors have seen a patient
 Which students are in a class
 Which item is selling the most on Friday’s
Primary key
 A primary key is a field, or a collection of fields, whose values uniquely
identify each record in a table.
 A primary key does not allow Null values or no blank values and must
always have a unique value.
 A Primary Key is a field that will contain no duplicates

You do not have to define a primary key, but it's usually a good idea. If you
don't define a primary key, Microsoft Access asks you if you would like to
create one when you save the table.
 To do this, simply select the # field and select the primary key
button
After you do this, Save the table
 Primary Keys
CheckNo LastName Phone College
7830276 Smith 273-5051 UDSM
2390045 Thomas 392-5555 IFM
9036723 Michael 846-5656 WI

To ensure that each record is unique in each table, we can set one field to be a
Primary Key field.
While each column in this particular data set has unique data, the field that
will work best for us is Check No. Many employees will work for the same
college, have the same last name and possibly even share telephone numbers,
but each employee should have a unique Check No. When there is not a
unique field in your data set, you can use an AutoNumber. Access can create
incremented or random AutoNumbers for your primary key.
 Foreign key
When you include the primary key from one table as a
field in a second table to form a relationship between
the two tables, it is called a foreign key in the second
table.
A primary key is used to relate a table to foreign keys in
other tables.
Ms Access Objects
 Tables
 Forms
 Queries
 Reports
 Pages
 Macros
 Modules
 Tables

 Tables: - a collection of data about a topic arranged

in rows and columns.
 Store the data in a database – similar to a
spreadsheet, but more organized. Contains records
(rows) and fields (columns).
 There are two types of table views:
1. Datasheet View
2. Design View
 Field Properties – input mask, validation rule, data
type, format, primary key, etc.
 Tables organize data into columns (called fields) and
rows (called records).

Fields

Records CheckNo LastName Phone College

7830276 Smith 273-5051 UDSM
2390045 Thomas 392-5555 IFM
9036723 Michael 846-5656 WI
 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 student admission
table might include fields for first name,
last name, address, region, district, and
phone number.
 Record: a record is a row in a table and is
a set of values defined by fields. In a
admission table, each record would
contain the data for one person as
specified by the intersecting fields.
Access data type

Make certain the field type you select matches the data to be held in that field.
Access data type (con’t)
Additional Access field types.
 Access data type (cont.)
 Data type: data types are the properties of
each field. A field only has one data type,
such as Character, Number or Date.
 Access Data Types examples
 Text Use for text or combinations of text and numbers, such as addresses, or for
numbers that do not require calculations, such as phone numbers or postal codes
(255 characters) Example Legal Name John Doe.
 Memo Use for lengthy text and numbers, such as notes. Stores up to 65535
characters
Example Notes: Sally displays a high amount of...
 Number Use for data to be included in mathematical calculations, except money:
Age: 31
 Date/Time Use for dates and times: Example Birthday: April 7, 1969
 Currency Use for currency values and to prevent rounding off during calculations.
Price: Tsh. 84.95
 AutoNumber Use for unique sequential that are automatically inserted with a new
record :Invoice Number:187001
 Yes/No Use for data that can be only one of two possible values, such as Yes/No,
True/False, On/Off. Employed? Yes
 OLE Object Use for OLE objects (such as Microsoft Word
documents, Microsoft Excel spreadsheets, pictures, sounds

 Hyperlink Use for hyperlinks (hyperlink: Colored and

underlined text or a graphic that you click to go to a file, a
location in a file, a Web page on the World Wide Web, or a
Web page on an intranet. Stores up to 2048 characters of a
clickable link to file on your computer to web pages on the
internet. www.amazon.com
 Lookup Wizard Use to create a field that allows you to
choose a value from another table or query.
 Create a Table from scratch in Design view
Switch to the Database Window.
You can press F11 to switch to the Database window from any other window.
 Double-Click on "Create table in Design view".

Define each of the fields in your table.

 Under the Field Name column, enter the categories of your table.
 Under Data Type column, enter the type you want for you categories .
(The attribute of a variable or field that determines what kind of
data it can hold. For example, in a Microsoft Access database, the
Text and Memo field data types allow the field to store either text or
numbers, but the Number data type will allow the field to store
numbers only. Number data type fields store numerical data that
will be used in mathematical calculations. Use the Currency data
type to display or calculate currency values. Other data types are
Date/Time, Yes/No, Auto Number, and OLE object (Picture).
 Tables

Tables store the data

Entering Data

 Click on the Datasheet View and simply

start keep going by entering the data into
each field.
Manipulating Data
 Adding a new row
 Simply drop down to a new line and enter the information
 Updating a record
 Simply select the record and field you want to update, and
change its data with what you want
 Deleting a record
 Simply select the entire row and hit the Delete Key on the
keyboard
Forms
 Forms are used for entering data,
displaying data, update data tables
or for building a menu system.
 Types: Basic Forms, Split Forms,
Multiple Item Forms, Subforms
 Forms cont.
You can add, update, delete records in your table by
using a form.
NOTE: Although a form can be named different from a
table, they both still manipulate the same information
and the same exact data. Hence, if you change a record
in a form, it will be changed in the table also.
A form is very good to use when you have numerous
fields in a table. This way you can see all the fields in
one screen, whereas if you were in the table view
(datasheet) you would have to keep scrolling to get the
field you desire.
 Create a Form using the Wizard

The following steps are needed to create a basic form using the wizard:
 Click on the Forms button under Objects on the left side of screen
 Double click on Create Form Using Wizard
 On the next screen select the fields you want to view on your form. Most of the time you
would select all of them.
 Click Next
 Select the layout you wish
 Click Next
 Select the style you desire...HINT: if you plan on printing your form, I suggest you use a
light background to save on printer toner and ink
 Click Next
 Give your form a name, and select Open the Form and enter information
 Select Finish
 You should see your form. To adjust the design of your form, simply hit the design button
(same as with the tables), and adjust your form accordingly
 Example of Forms
Used for data input, menus, display and
printing
What is an Access query?
 Queries: - a tool that lets you
view, change, and analyze data in
different ways
 Allow you to ask questions
(queries) about data in the
database to extract information.
 When have customers placed
orders?

What is an Access query?
(cont.)
 You can create a query when you need ONLY a portion of the data
from tables (or existing queries).
 For example, you may only need to see customers who live in
Mwanjelwa. The response would be to display only the records whose
state field matches with Mwanjelwa.
 Multiple tables or queries can be used.
 Restrictions can be used
 Comparison operators
 The design view is used to specify the fields and records you want
to see.
 Queries
Can look across a number of Tables and other
Queries
Can perform Calculations and Combine fields
Expressions
Reports
 Reports: - a method to present your data in a
printed format, such as charts & invoices
 Used to format and present the data in a
professional, logical and useful manner. Often
intended for printing and distribution. Can be built
from tables or queries.
 Reports (output) in a system dictate what data
must be stored in the database.
 Report, Layout, Design and Print Preview
Creating a Report
 As with the Form, it is a very good idea to create a report using the wizard, unless you are an
advanced user. Microsoft Access does a very good job using the wizard to create reports.
 Click on the Reports button under Objects on the left side of screen
 Double click on Create Report Using Wizard
 On the next screen select the fields you want to view on your form. Most of the time you
would select all of them.
 Click Next
 Select if you would like to group your files. Keep repeating this step for as many groupings as
you would like.
 Click Next
 Select the layout and the paper orientation you desire
 Click Next
 Select the style you desire...HINT: if you plan on printing your report, I suggest you use a light
background to save on printer toner and ink
 Click Next
 Give you report a name, and select Preview the Report
 Select Finish
 You should see your report. To adjust the design of your report, simply hit the design button
(same as with the tables), and adjust your report accordingly
 Reports (cont…)
 Output of information in a printed report
 Allows you to group and summarize data
Basic Design Rules
 Organizing Data
Once you’ve chosen your fields, you need to decide if they
belong in different tables. Data should be kept in separate tables
if you have an indeterminate number of entries. One employee
can have a number of evaluations.
EMPLOYEE TABLE
Emp ID First Last Eval 1 Eval 2
Name Name
123-456 Sallye Shapiro 1/15/2010 1/14/2011
125-985 Samuel Smith 1/12/2011 EVALUATION TABLE
248-890 Sidney Samueson Emp ID Eval Date
123-456 1/15/2010
123-456 1/14/2011
123-985 1/12/2011
Basic Design Rules
 No Derived Fields
If a field you are not using as a link exists in another table, it
should not be repeated in the current table. Listing it in both
places leads to data entry errors. Since we have the Emp ID in
both tables, there is no need to include the Employee’s Last
Name in the Evaluation table.
EVALUATION TABLE
EMPLOYEE TABLE Emp ID Last Name Eval Date
Emp ID First Name Last Name
123-456 Shapiro 1/15/2010
123-456 Sallye Shapiro
123-456 Shapiro 1/14/2011
125-985 Samuel Smith
123-985 Smith 1/12/2011
248-890 Sidney Samueson

You can use a query to pull values from both tables into one datasheet.
Basic Design Rules
Data is broken down into Smallest Logical Parts
Each segment of data you want to sort or filter should be kept in its own field.
For example, what if I needed to sort by City or Region or District? Pulling
fields together is fairly simple, pulling them apart can difficult.

ID Home Address
987 123 West Main Street,
Gainesville, FL 32601
654 456 South 3rd Road, Apt 12,
Newberry, FL 32684

ID Addr1 Addr2 City State Zip

987 123 West Main Street Gainesville FL 32601
654 456 South 3rd Road Apt 12 Newberry FL 32684

You can join fields together in queries, forms and reports.

Basic Design Rules
 Descriptive Field Names
Be careful of using too many abbreviations in your field
names. You have up to 64 characters, but long field names
can be difficult to use in expressions. Be Clear, Be Concise
and Be Consistent.
EMPLOYEE TABLE
ID FN LN DOB DOH SSN CMT
1234 Sallye Shapiro 6/17/1970 7/02/2001 123-450 N/A

EMPLOYEE TABLE
Emp Emp First Emp Last Emp Birth Emp Hire Emp System Emp
ID Name Name Date Date Signal # Comments
1234 Sallye Shapiro 6/17/1970 7/02/2001 123-450 N/A
Basic Design Rules
 No Calculated Fields
In Microsoft Excel we enter the data and create our formulas all
at once. In Access you are creating a “Data” table, a table of the
raw data. If you want Access to do the calculations, you can
create an expression elsewhere in the database.
Emp ID Hourly Hours Pay
Rate worked
123 $10.00 40 $390.00

Pt Med Height Weight BMI

Rec (m) (kg)
456-456 2 91 23

You can create calculated expressions in queries, forms and reports.

Opening a
Database
 To open a database when
you start Access
– Choose the database you
wish to open from the left
hand panel, or choose
More… to browse for
another database

 To Create a database, click on

the Blank Database at the top
of the middle panel

 You can also use the

Microsoft button to open
existing databases or create
new ones.
The Access Database Window

The navigation pane on

the left-hand organizes all
the database objects
 Data View/Design View

Datasheet
View

Design
View
 Navigating Fields and
Records
 To move through records and fields
 Tab  Home/End  Page Up
 Shift+Tab  Ctrl+Home  Page Down
 Enter  Ctrl+End  The Arrow Keys

 To move through records

Previous Record Next Record New Record

First Record Current Record Last Record

THE END

THANKS
 EXRCISE
• Create a database save it as
BIOMEDICAL 2nd YEAR
• Create a table with the following fields:
REGNO will include the primary key
• STUDENT FIRSTNAME
• STUDENTLASTNAME
• STUDENTGENDER
• STUDENT BIRTHDATE
• STUDENTHOME ADDRESS
• STUDENT CITY
• STUDENT REGION
• STUDENT DISTRICT
• STUDENT PHONENUMBER