Introduction to database concepts and access
This presentation is the property of its rightful owner.
Sponsored Links
1 / 138

Introduction to Database Concepts and Access PowerPoint PPT Presentation


  • 85 Views
  • Uploaded on
  • Presentation posted in: General

Introduction to Database Concepts and Access. Using Access 2000 - Foundation/Intermediate. What is Data?. Data - facts made up of text, numbers, images and sounds - Murray 15000 10

Download Presentation

Introduction to Database Concepts and Access

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Introduction to database concepts and access

Introduction to Database Concepts and Access

Using Access 2000 - Foundation/Intermediate


What is data

What is Data?

  • Data - facts made up of text, numbers, images and sounds -Murray 15000 10

  • Information is the meaning given to data in the way it is interpreted:Mr Murray is a sales person whose basic annual salary is $15,000 and whose commission rate is 10%


What is a database

What is a Database?

  • A structured collection of related data

  • An address book, a Telephone directory, a Timetable etc.


Basic concepts

Name: David MurrayCompany: CCTTel: 01242 227200

Basic Concepts

  • File

    • A set of related records

  • Record

    • A collection of Data about an individual item

  • Field

    • A single item of data common to all records

Name: David MurrayCompany: CCTTel: 01242 227200

Name: David Murray


The telephone directory an example of a database

The Telephone Directory - An Example of a Database

Telephone Directory

Aardvark A.A. Railway Cuttings Cheltenham (01242) 123456

Aardvark S.F. 23 High Street Cirencester (01285) 654321

Aaron A.M. The Paddock Cheltenham (01242) 101010

File Name

Records

Fields


Tables and relationships

Tables and Relationships

  • Customermakesorder

  • Orderconsists of order details

make

consist

of

have

deal with


Types of relationships

One-to-one

Many-to-many

Types of Relationships

  • One-to-many

  • One-to-one

  • Many-to-many resolved into two one-to-many

One-to-many


Why use an electronic database

Why Use an Electronic Database?

  • Speed

  • Ease of Use

  • Versatility


Why use access

Why Use Access?

  • Familiar look and feel of Windows

  • Easy to start building simple databases

  • Can build sophisticated systems

  • True relational database

  • Allows prototyping


An introduction to access

An Introduction to Access

Each column represents a field within the record

Each line represents a record within the table


Introducing access tools

Customer

Company Name

Address

City

Telephone

Contact Name

Introducing Access Tools

Tables

Employees

Customers

Reports

Forms

Queries


Using access as part of microsoft office professional

Using Access as Part of Microsoft Office Professional

  • Microsoft Office Professional includes:

    • Access

    • Word

    • Excel

    • PowerPoint

    • Outlook


Starting microsoft access

Starting Microsoft Access

  • Click on “Microsoft Access” in the Start menu

Or if you have set up a shortcut on your Desktop, click on the Access shortcut icon


The access application window

The Access Application Window


Access concepts terminology and usage

Access - Concepts, Terminology and Usage

Using Access 2000 - Foundation/Intermediate


Opening a database

Opening a Database

  • To open a database when you start Access

    • Choose the Open an existing file option on the opening dialog, as illustrated

  • To open a database once you have already started Access- Choose Open from the File menu- OR press CTRL+O- OR click on the Open icon on the toolbar


The access database window

The Access Database Window

  • Icons down the left hand side provide access to all database objects

    • Select the object by clicking the icon


Closing a database

Closing a Database

  • To close a database

    • Choose Close from the File menu

    • Or click on the Control menu and select Close

    • Or press CTRL+F4


Opening a table

Opening a Table

  • To open a table-Click on the Table icon in the Database window- Select the table you want- Click on the Open icon


The table window

The Table Window

  • A table opened from the database window appears as a datasheet

  • Each row contains a separate record

  • Each column contains a separate field


Exploring the table

First record

Previous record

Next record

Last record

Exploring the Table

  • To move through records and fieldsuse TAB, SHIFT+TAB, HOME, END, CTRL+HOME, CTRL+END, PAGE UP, PAGE DOWN, and the arrow keys

  • To move through records


Table design view

Table Design View


Introducing queries

Introducing Queries

  • A means of asking questions of your database

  • Can look across a number of Tables


Introducing forms

Introducing Forms

  • A friendlier view of the database

  • Used for data input, menus, display and printing


Types of form

Types of Form


Opening an existing form

Opening an Existing Form

  • To open a form


Form design view

Form Design View

  • A form can be viewed in

    • Datasheet view

    • Design view

    • Form View


Introducing reports

Introducing Reports

  • Output of information from your database in the form of a printed report

  • Allows you to group and summarize information

  • Can be previewed to the screen prior to printing

  • Can include logos, graphs and drawings


Database design and table creation

Database Design and Table Creation

Using Access 2000 - Foundation/Intermediate


Design and document your database

Design and Document Your Database

  • A designers best tools are a pencil and paper

    • It is important to plan what you are going to do

  • The sooner you touch the computer the sooner you’ll make a mistake

    • If you don’t plan you will often have to start again

    • Document what you are doing, will you rememberwhat you did in three months time?


Questions to ask yourself

Questions To Ask Yourself

  • What do I want?

    • (Outputs)

  • What have I got?

    • (Inputs)

  • What do I need to do to get there?

    • (Process)


Define your needs

Define Your Needs

  • Draw a picture

  • Write a description

PRODUCTS

CATEGORIES

SUPPLIERS

ORDERS

C.C. Toys

EMPLOYEES


Basic design rules

Basic Design Rules

  • Unique records

  • Unique fields

  • Functionally dependent fields

  • Independent fields

  • No calculated or derived fields

  • Data is broken down into smallest logical parts

RULES


Determine relationships

one

many

many

one

Determine Relationships

  • Customer makes many orders: one-to-many

  • Order contains many products and products can appear on many orders: many-to-many

  • Employee belongs to social club: one-to-one

  • Get rid of many-to-many by introducing another table, e.g. Order Details


Creating a database

Creating a Database


Using the table wizard

Using the Table Wizard


Adding fields using the table wizard 1

Adding Fields Using the Table Wizard (1)

BUSINESS

PERSONAL

OR


Adding fields using the table wizard 2

Adding Fields Using the Table Wizard (2)


Creating a table without a wizard

Creating a Table Without a Wizard

OR


Adding fields to a new table

Adding Fields to a New Table

  • Type Fieldname

  • Choose Data Type

  • Type Description

  • Enter Field Properties


Field properties

Field Properties


The input mask property

The Input Mask Property

  • Allows you to specify the format of input

  • Useful if input always follows a standard format

    • ZIP or Post codes

    • Telephone Numbers

    • National Insurance codes

  • UK Post code

    • >LL09\ 0LL

  • UK Telephone Number

    • \(99999") "000000


Setting a primary key

Setting a Primary Key

  • In Table Design View

    • Select the field you wish to use as the Primary Key

    • Click on Primary Key Button


Saving a table

Saving a Table

  • To save a table

    • Choose Save from the File menu

    • Enter a table name if this is the first time you have saved the table

    • Click OK


Adding records to a table datasheet

Adding Records to a Table Datasheet

  • Click here and start typing

  • Pressing TAB moves you to the next field

  • When in the last field of the record pressing TAB moves you to the next record


Formatting a table

Formatting a Table

Using Access 2000 - Foundation/Intermediate


Editing records

Editing Records

  • Many editing operations involve selection

  • There are many ways to select fields and records

  • Record selectors indicate the current status of the record

Current record

Record is selected

Record is being edited

Last (empty) record


Selecting records with the mouse

Selecting Records With The Mouse

  • Group of Records

    • Click and drag across record selectors

  • All Records

    • Click here

  • Single Record

    • Click in the record selector


Selecting records using the keyboard

Selecting Records Using The Keyboard

  • Single Record

    • Select the record required

    • Select the Edit menu (Alt+E)

    • Choose Select Record (L)

  • All Records

    • Select the Edit menu (Alt+E)

    • Choose Select All Records (A)or

    • Press Ctrl+A


Selecting fields with the mouse

Selecting Fields With The Mouse

  • Single Field

    • place the mouse at the beginning of the field (cross pointer) and click once

  • Group of Fields

    • Select 1st field

    • Hold [Shift] + select last fieldor

    • Select and Drag


Selecting columns with the mouse

Selecting Columns With The Mouse

  • To select a Field Column

    • Click the button above the column

  • To select Adjacent Columns

    • Click the button above the column and drag across the columns required


Selecting fields and columns using the keyboard

Selecting Fields and ColumnsUsing the Keyboard

  • Single Field

    • Use the TAB key until the required field is selected (left to right)

    • Use Shift+TAB (right to Left)

  • Adjacent Fields

    • Select the first field (as above)

    • Hold the Shift key

    • Move in the required direction using the cursor keys


Deleting records and fields

Deleting Records and Fields

BEWARE!

  • Select the item(s)

  • Press the Delete key


Viewing or setting datasheet default values

Viewing or Setting Datasheet Default Values

  • To set defaults

    • Select the Tools drop down menu

    • Select the Options command

    • Select the Datasheet tab


Re ordering columns within a table

Re-ordering Columns Within a Table

  • Select Column to be moved

    • Click on Column button

  • Click and drag to the new location

    • Note black bar

  • Release and the column is moved


Re ordering fields in the table

Re-Ordering Fields in the Table

  • In Design View

  • Select the Field to be moved

    • Click on the Field Select button

  • Click and drag to a new location

    • Note black bar

  • Release and the Field is moved


Changing column width and row height

Changing Column Width and Row Height

  • Or

    • Click on the column header and right click

    • Select Column Width

    • Enter the column size or

    • Choose Best Fit

  • To size a Column

    • Move the mouse to point between the columns until this symbol is displayed

    • Click and drag to the width required and release


Hiding and un hiding columns

Hiding and Un-hiding Columns

  • To hide a Column

    • Select the column(s) you wish to hide

    • Click the right mouse button

    • Select Hide Columns

  • To show a Column

    • Select the Format menu

    • Choose Unhide Columns

    • Select the fields you wish to show

    • Select Close to action choices


Freezing columns

Freezing Columns

  • Freezing Columns

    • Allows you to keep selected columns visible on-screen whilst you view columns off the screen

  • To Freeze Columns

    • Select Columns

    • Click the right mouse buttonorSelect the Format menu

    • Click Freeze Columns

  • To Unfreeze Columns

    • Select the Format menu

    • Click Unfreeze All Columns


Formatting datasheet cells

Formatting Datasheet Cells

  • Cell Effects include:

    • Horizontal and vertical gridlines

    • Gridline and background colors

    • Cell effects - Flat, Raised and Sunken


Changing datasheet fonts

Changing Datasheet Fonts

  • Select Font from the Format drop down menu


Copying and moving fields and records

Copying and Moving Fields and Records

  • To copy or move fields and records

    • Select the field or record

    • Copy or move it to the Clipboard

    • Paste from the Clipboard

Cut - move (Ctrl+X)

Copy (Ctrl+C)

Paste (Ctrl+V)


Another look at the database window

Another Look at the Database Window

  • From the Database Window you can:

    • Copy, Rename and Delete objects

    • When you copy and paste a table you give it a different name


Printing

Printing

  • Choose Print Preview before you print


Relationships

Relationships

Using Access 2000 - Foundation/Intermediate


Defining relationships

Defining Relationships

  • In order to set relationships you need to carry out three operations

    • Open the Relationships Window

    • Add the Tables

    • Set the Relationships


Opening the relationship window

Opening the Relationship Window

  • Open the Database Window

  • Click on the Relationships icon on the toolbar


Adding tables to the relationships window

Adding Tables to the Relationships Window

  • Click on the Show Table icon

  • Select the table(s) required in order to build the relationshipclick on Add


Making relationships

Making Relationships

  • Click on the field in the primary table and drag to the corresponding field in the secondarytable

  • The Edit Relationshipswindow appears

  • Click on the Create button


Referential integrity

Referential Integrity

  • Referential integrity helps you ensure the relationships between records are valid

It ensures you don’t delete related data


Editing relationships

Editing Relationships

  • You can:

    • Display all relationships

    • Display only direct relationships

    • Delete a relationship

    • Remove a table from the Relationships window


Locating and replacing information

Locating and Replacing Information

Using Access 2000 - Foundation/Intermediate


A simple search

A Simple Search

  • Open the table to search

  • Click the Find icon

  • Enter requirements into the dialog box

  • Select Find First or Find Next


Wildcards

Wildcards

  • Wildcard symbols are codes used to allow you to make complex searches for information

  • The Symbols

     - any group of characters in this position

    ? - any single character in this position

    # - any single digit in this position

    [ ] - square brackets for inclusions

    [! ] - square bracket and exclamation marks for exclusions


Finding specific values

Finding Specific Values

Examples:

  • Fr = Fred, Frank, Francis, France, French

  • J?ne = June, Jane

  • 199# = 1991,1992,1993,1994 . . . . . .1999

  • Jo[ha]n = John, Joan

  • Min[!t] = Mine, Mind, Mink ...... but not Mint


Find and replace

Find and Replace

  • Click on the Replace command under the Edit drop down menu


Sorting records

Sorting Records

  • Quick Sort allows you to quickly sort the table by your selected field

  • The sort can be either ascending or descending


Sorting records on more than one field

Sorting Records On More Than One Field

Leftmost columns are sorted first


What is a filter

What is a Filter?

  • Use a filter to temporarily filter out excess information

    • Filter out permanent employees

  • To narrow your focus

    • One customer record

  • Find records with complex criteria

  • Sort records on more than one field and in more than one direction


Filtering records by selection

Filtering Records By Selection

  • Use Filters to get a subset of records sharing a common attribute

Filter by Selection


Filtering records by form

Filtering Records by Form


Creating simple queries

Creating Simple Queries

Using Access 2000 - Foundation/Intermediate


What are queries

What are Queries?

  • Queries help you select information from tables or queries for a specific purpose

  • You can select fields from records

  • You can select records from a table or query

  • You can select, summarize, update, delete, make new tables and append records to another table


Opening an existing query

Opening an Existing Query


Creating queries using the simple query wizard

Creating Queries Using the Simple Query Wizard


Creating queries without the wizard

Creating Queries Without the Wizard


The query grid

The Query Grid

Tables or queries

All

Required

Fields

Ordering

(left to right)

Record Restrictions

Visible


Logical operators in criteria

Logical Operators in Criteria

  • When setting criteria for queries you use logical operators to define what you require

    • = (Equals/Same)

    • < ( Less Than/Lower)

    • > (Greater Than/Higher)

    • <= (Less than or equal to)

    • >= (Greater than or equal to)

    • <> (Not equal to)

    • And

    • Or

    • Like


Text in criteria wildcards

Text in Criteria - Wildcards

  • Wildcard symbols are codes used to allow you to make complex searches for information

  • The Symbols

     - any group of characters in this position

    ? - any single character in this position

    # - any single digit in this position

    [ ] - square brackets for inclusions

    [! ] - square bracket and exclamation marks for exclusions


Multiple criteria and alternative criteria

Multiple Criteria and Alternative Criteria


Calculated fields

Calculated Fields


Summary queries

Summary Queries

Allows data to be viewed in summary form

- Totals, counts

- Max, min

- First, last etc

Group By one or more fields


Update queries

Update Queries

Update of all entries in a column or…

Update of all entries matching

the specified criteria

Arithmetic operations

To specific values


Append queries

Append Queries

Add the results of a query to a table


Make table queries

Make-Table Queries

Use the results of a query

to make a new table


Creating calculated fields

Creating Calculated Fields

Using Access 2000 - Foundation/Intermediate


Creating calculated fields1

Creating Calculated Fields

  • It is often more efficient to calculate information (e.g. for a report) when it is needed rather than holding it in a table

  • Instead of having a monthly pay field, you could use an expression to calculate it from Salary divided by 12Monthly Pay: [Employees]![Salary]/12


Using the expression builder

Using the Expression Builder

Expression Box

CommonOperators

ObjectFolders

Elements that can be pasted into an expression

Elements that can be pasted into an expression


Introducing forms1

Introducing Forms

Using Access 2000 - Foundation/Intermediate


Introduction to forms

Introduction to Forms

  • Forms provide a friendlier view of the database

  • Forms can be used to display, view and print data

  • Forms can be used to add, update and delete records

  • Forms can include pictures, drawings, different fonts and colors


Basic form layout types

Basic Form Layout Types

Columnar

Tabular

Datasheet

Justified


Creating a form using autoform

Creating a Form Using AutoForm


Creating a form using the form wizard

Creating a Form Using the Form Wizard


Different ways of viewing forms

Different Ways of Viewing Forms


More about creating forms

More About Creating Forms

Using Access 2000 - Foundation/Intermediate


Creating forms without using the form wizard

Creating Forms Without Using the Form Wizard

  • You can either start with a clean canvas ready to add controls

  • Or you can start with a Wizard created form and modify it


The form design view window

The Form Design View Window


Manipulating form design

Manipulating Form Design

  • You can modify a form by adding controls

  • You can move, re-size and delete controls

  • Remember the basic Windows rule!

    • First you select by clicking in the control

    • Then you manipulate


Types of control

Types of Control

  • Bound controls are bound to fields in tables or queries

  • Unbound controls display information not held in the database

  • Calculated controls are derived from expressions

  • You can add controls using the toolbox


The toolbox

The Toolbox

  • Using the Toolbox you can add controls

    • Open the toolbox by clicking on the toolbox icon

    • Position and size the toolbox to your preferences

    • Double click on the toolbox title bar to attach it to the other menus at the top of the screen


Creating a combo box control using the toolbox

Creating a Combo Box Control Using the Toolbox

  • Create a new form

  • Click on the Combo Box icon

  • Follow the on screen instructions


Changing form properties

Changing Form Properties

  • Use the Property Sheet to view properties


Using forms with subforms

Using Forms With Subforms

  • Use in a one-to-many relationship

  • The main form is the “one”

  • The subform is the “many”

  • You can have more than one subform

  • You can have subforms within subforms

Form

Subform


Creating a form with a subform

Creating a Form With a Subform


Controlling data input

Controlling Data Input

  • Data input should be easy and as error free as possible

  • Default Value: automatically inserts a value for the field in each new record - it can be overtyped

  • Validation Rule: limits acceptable ranges and values

  • Validation Text: a message which appears when the rule is broken


Changing tab order

Changing Tab Order

  • Tab order governs the way you move from control to control on a form

    • Access automatically assigns a tab order based on the order in which the controls were created

    • Use the Tab Order dialog box to make changes


Adding command buttons

Adding Command Buttons

  • Command buttons are used to initiate sets of actions such as opening the next form or running a particular query

    • Some of these actions, called event procedures, are built in to Access


Adding page breaks

Adding Page Breaks

  • Use page breaks when your form covers more than one screen

  • When the user presses Page Up or Page Down the form moves to the nearest page break


Switchboards

Switchboards


Reports

Reports

Using Access 2000 - Foundation/Intermediate


Introduction to reports

Introduction to Reports

  • The traditional computer output

  • Preferred by many people

  • Use them for summarizing large amounts of data such as sales reports, stock lists, mailing lists, invoices etc.


Opening and viewing existing reports

Opening and Viewing Existing Reports


Printing reports

Printing Reports

  • You can print from the database window, from Design View or Print Preview


Creating reports using autoreport

Creating Reports Using AutoReport


Creating reports using the report wizard

Creating Reports Using the Report Wizard


Using the label wizard

Using the Label Wizard


Using the chart wizard

Using the Chart Wizard


Grouping levels in reports

Grouping Levels in Reports

  • You can use Report Wizard to add grouping levels


Creating a report without a wizard

Creating a Report Without a Wizard

When you choose not to use a Wizard you are presented with a blank “Report” onto which you place controls, as when creating a “Form”


Creating controls

Creating Controls

  • There are three types of controls

    • Bound controls are bound to fields in tables or queries

    • Unbound controls display information not held in the database

    • Calculated controls are derived from expressions

  • You can add controls using the Toolbox


Setting properties

Setting Properties


File management within access 2000

File Management Within Access 2000

Using Access 2000 - Foundation/Intermediate


Access 2000 properties

Access 2000 Properties

  • From the File menu, choose the Database Properties command

    • Displays information about the current database


Opening files a review

Opening Files - A Review

  • Sometimes known as loading a file

    • Note: A list of the four most recently opened files is displayed under the File menu

    • You can open these files by clicking on them


Selecting files

Selecting Files

  • To mark sequential files

    • Click on the first file

    • Depress the Shift key

    • Click on the last file of the range you wish to select

    • Release the Shift key

  • To mark non-sequential files

    • Click on a file

    • Depress the Ctrl key (and keep it depressed)

    • Click on other files you wish to select

    • Release the Ctrl key


Finding files using access 2000

Finding Files Using Access 2000

  • You may search for a file if you know either:

    • The file name

    • A word or phrase contained within the file


Copying deleting renaming files and creating shortcuts

Copying, Deleting, Renaming Files and Creating Shortcuts

  • Display the Open dialog box

  • Select a file and right click using the mouse

  • Select the required command from the pop-up displayed


Setting a default folder directory

Setting a Default Folder (Directory)

  • By default normally points to My Documents

  • May be customized as you wish


  • Login