Relational Database Software

1
Microsoft Access 2000

• Relational Database Software

2
Note before we begin Converting a database

• You CAN convert Access 95, 97 directly to 2000
• You CANNOT convert Access 2000 directly to 95 or
  97
• Save it down gt Tools/Database Utilities/Convert
  Database/to Prior Access Version

3
General Concepts
4
What is a Database and what is it used for?

• A database is an organized collection of data
  related to a particular topic or purpose.
• The primary function of a database is to enable
  the user to organize and retrieve information in
  a manner defined by the user.

5
Access

• Relational database software with a graphical
  user interface.
• Access integrates easily with Excel Word.
• Easy to get started with pre-made tables, forms,
  queries reports.
• Can use to create database applications by
  incorporating macros modules (Visual Basic).

6
Flat-File vs. Relational

• A Flat-file database consists of a single
  database file or table which contains all the
  information about a topic. It does not
  physically link or point to other files.
• A Relational database consists of multiple tables
  linked together by at least one common field.

7
STUDENT INFORMATION TABLE
8
Products Table
Supplier Table
9
The Database window

• Menus and toolbars
• Command Center all operations start here
• Categories of objects on left
• Objects and creation icons on right
• Status bar

10
Access Database Objects

• Tables
• Forms
• Reports
• Queries
• Macros
• Modules
• Pages

11
Tables

• Tables are the heart of Access.
• All data stored in tables. Fields (columns) and
  records (rows) intersect to form cells.
• Each table should contain information about one
  subject only.
• Each field contains a specific type of
  information such as text, number, currency,
  dates, etc.

12
Tables (cont.)

• Two or more tables are linked together through
  fields they have in common. This is done by
  defining relationships.
• Access creates relationships between different
  tables, about different but related subjects
• A table should contain data about 1 topic only,
  e.g., not students and faculty in one.

13
Queries

• Create a subset (dynaset) of data.
• Means for obtaining data from 1 or more related
  tables.
• Used to sort and/or select records according to
  your criteria.
• Used as the basis for reports.
• Action queries can be used to update, archive,
  delete, append records, etc.

14
Forms

• Used to view, edit, and enter data. You can also
  enter data directly into a table. They can be
  customized for convenience. Best for on-screen
  viewing.
• Forms may be designed to simplify data entry and
  data editing.
• Can use controls to make data entry easier and
  more consistent such as drop-down lists, radio
  buttons, etc.

15
Reports

• Sorted and summarized data. They can be designed
  to show only the data you want to show. Best for
  printed materials.
• Used for printing data in an organized,
  professional looking manner.
• Can add subtotals, groupings, etc. to reports.

16
Macros

• Automatically carry out one or more tasks. Best
  for tasks that you perform often.

17
Modules

• A set of procedures stored as a unit to perform
  an action. Written in Visual Basic.

18
Pages

• A data access page for viewing on the Internet,
  or an intranet.

19
Table Design vs. Table datasheet view

• Toggle in the upper right corner. Flips between
  the data itself (table) and the underlying
  structure (design) of the data.
• Datasheet allows you to enter data, sort and
  filter data, hide and freeze columns, etc.
• Design allows you to create tables, add fields,
  and set and modify field properties.

20
Navigating the datasheet

• Navigation buttons in lower right hand corner
  one record at a time, beginning, end, new record,
  or type the number in.
• PgUp or PgDn for page at a time
• CTRL Home for beginning, CTRL End for end
• F5 moves you to record box, type record number,
  enter

21
Resizing rows and columns

• Similar to Excel
• Click and drag border
• D-click right border for autofit

22
Sorting

• Automatically sorted by primary key (unique
  identifier assigned to every table)
• Select sort column, hit ascending or descending
  sort button
• Can select multiple adjacent columns will sort
  left to right
• Very simple filters and queries for more
  flexibility

23
Selecting data in datasheet mode

• Part of cell - Click and drag
• Entire cell Click left edge ( mark)
• Adjacent fields Click left edge and drag
• Column Click top of column (black arrow)
• Row Click side of row (black arrow)
• Multiple columns Click and drag black arrow
• Multiple rows Click and drag black arrow
• All CTRL A or upper left box

24
Rearranging, hiding, freezing

• To move a column, select, then drag
• To hide a column, select it, Format/Hide
• For confidential information
• Format/Unhide to restore
• To freeze a column, select it, Format/Freeze
• Will go to left side
• To keep it in view as you scroll
• Format/Unfreeze to restore

25
Printing datasheets

• Hide and/or size columns for best view
• Change formats, fill colors
• File/Page setup for orientation and columns
• Landscape is often best orientation
• Preview before printing

26
Manipulating data
27
Adding records

• ? for new record
• Always add to bottom Access will resort when
  table is closed
• No need to save when you leave record, Access
  will save
• Records/Data entry for data entry mode
• Current record only less distracting
• To remove Records/Remove Filter

28
Editing records

• Click inside field (pencil will appear on left)
• Saves when you leave row or close table
• Can only Undo last record, so BE CAREFUL
• Within same record hit escape to Undo

29
Deleting data

• Within a single field, select and hit Del
• Select records or multiple records from left, hit
  Del
• It will warn you once no Undo, no exiting
  without saving changes
• Large groups of deletes use delete query (Voter
  records purge)

30
Copying, moving data

• Edit/Copy, Edit/Cut, Edit/Paste
• CTRL C, CTRL X, CTRL V
• Use toolbar buttons
• You must select the same number of fields to
  paste into that you copied from
• CTRL - current date (doesnt always work)
• CTRL - copies data immediately above

31
Finding data

• Edit/Find
• Choice of column or table
• Choice of all or part of text
• Shift F4 for future searches

32
Filtering Records
33
Filtering Data

• By Selection
• Simplest cannot sort at the same time
• Can also filter excluding selection
• By Form
• Can use and or statements cannot sort at the
  same time
• Advanced Filter
• Use a filtering grid to enter expressions and
  sort criteria most like query grid can sort
  simultaneously, offers most flexibility.

34
Filter by selection

• Select data you wish to look for
• Hit Filter by selection button
• All records that match will appear
• Multi-filter, by adding criteria
• Remove filter button when finished

35
Filter by form

• Best for simple multi-criteria
• Data entry form will appear
• Type in criteria, hit filter by form button
• Remove filter button when finished

36
Advanced Filter/Sort

• Records/Filter/Advancrd filter-Sort
• Most versatile of three
• Can sort and filter in one step
• Only kind that can be saved (as a query)
• File/Save as Query

37
Advanced Filter/Sort Operators

• gt greater than
• gt greater than or equal to
• lt less than
• lt less than or equal to
• ltgt not equal to
• wildcard

38
Advanced Filter/Sort Operators (cont)

• Not - eliminates criteria from evaluation
• Between finds criteria within a range
• Like used with wildcard ()
• Null no value

39
Examples of Advanced Filter/Sort Operators

• CA - value is equal to CA
• gtT - value begin with letter T, through Z
• Is not Null - there is some value
• ltgtCA, Not CA - All values but CA
• gt1/1/91 - Date is on or after 1/1/91
• Like G - Values that start with G
• Not like oak - All words that dont have oak
  in them

40
Advanced Filter/Sort criteria

• Records/Filter/Advanced Filter-Sort
• Drag or D-click or pulldown fields that are
  involved
• Specify sort (ascending, descending, neither)
• Specify criteria using operators, text, numbers
• Hit Apply Filter

41
Multiple criteria and vs. or

• If you are looking for an and relationship,
  place both criteria on same line
• Vertical lines between boxes are and
  relationships
• If you are looking for an or relationship,
  place criteria on different lines
• Horizontal lines below criteria are or
  relationships
• Or statements can be typed together in same box
  (R-click for zoom)

42
Practice session

• X to clear grid
• A list of orders sent to Switzerland sorted by
  shipping date (18 records)
• A list of orders shipped outside the USA in 1996
  (123 records)
• A list of orders with freight 50 or over to
  Germany or Brazil sorted by amount (90 records)

43
Database Design
44
Figuring out what you need

• Determine the purpose of the database. The
  subjects that need to be included and the facts
  you need about each subject.
• Determine the tables you need. Divide
  information into separate subjects, and have a
  different table for each subject.
• Determine the fields you need. Decide what
  information will be stored in each table. Break
  the information down to its smallest logical parts

45
Figuring out what you need (cont)

• Determine which field will be the unique
  identifier (key field). If there isnt one (i.e.
  SS), Access will supply one. Names are not
  unique, and addresses and phone numbers can
  change.
• Determine the relationships. Decide how the
  tables relate to each other. Add fields and/or
  tables to help clarify these relationships.

46
Sample database design

• You have a list of workshops, people who came,
  people who presented, how much they spent
• You want expenses by person, by workshop, by
  category, various sub-totals
• You want lists of attendees by workshop, by
  location, by interests
• You want to create mailing lists for future
  workshops, based on interests and/or location

47
4 interrelated tables

• Table of workshops
• Table of attendees
• Table of presenters
• Table of presenter expenses

48
Workshop index

• W - key field
• Name of workshop (subject/city)
• Facility
• Address 1
• Address 2
• City
• State
• Zip
• Date of workshop
• General Subject of workshop

49
Attendee listings

• A - key field
• W - (pulldown menu of subject/city)
• Prefix
• FName
• LName
• Phone number
• Address 1
• Address 2
• City
• State
• Zip

50
Presenter listings

• P - key field
• Prefix
• FName
• LName
• Phone number
• Address 1
• Address 2
• City
• State
• Zip
• Yes/no field for each workshop subject

51
Presenter expenses

• E - key field
• W (pulldown menu of subject/city)
• P (pulldown menu of presenters)
• Date
• Travel expenses
• Food expenses
• Lodging expenses
• Materials expenses
• Misc. expenses

52
Creating the tables

• Files/New/Database/Create to start from scratch
• Table wizard will leads you through decisions
• Design view toggles with datasheet view

53
Setting the primary key

• This is step 1
• Default ordering by this field
• Access wont allow duplicates
• Helps define and create relationships between
  tables
• Usually best to have Access assign one (SS,
  pre-existing employee are exceptions)
• Hit the key icon
• Set data type as Autonumber

54
Field properties upper grid

• Field Name no spaces or other applications
  cant read it
• Data type number ONLY if you perform math
  functions (Zip Code, phone numbers best as text)
  default is text
• Lookup wizard to set up a pull down box
  (pre-existing or new list)
• Description will show in status bar let
  others know what the field is for, how to enter
  data, etc.

55
Field properties lower grid

• F6 toggles between upper and lower
• Each field created in upper grid has a set of
  properties defined in lower grid

56
Field size

• Maximum number of characters an entry could be
• 50 is default for text field
• 250 is maximum for a text field
• 64,000 is the maximum for memo field

57
Format

• Displays numbers and text in a certain format,
  usually numeric dates, currency.
• Different formats displayed for different data
  types (text has none listed)
• Some text formatting lt is all lowercase, gt is
  all uppercase, _at_ will display no data when
  nothing is entered

58
Input mask

• Inserts characters that arent saved in data, but
  help the imputer format data phone numbers,
  Social Security numbers, dates
• Hit the button to see options

59
Caption

• Specifies a label other than the field name
• Spaces can be used here, as opposed to the field
  name
• Default is field name

60
Default value

• Automatically fills in a specific value current
  date is most common
• Date() gives current date
• CO for home state
• Value can be changed after the fact

61
Validation rule

• Limits data to certain restrictions
• Use Expression Builder () or just type it in
• Use same standard operators from advanced filters
• gt50
• 25 or50 or75

62
Validation text

• Message displayed when data breaks violation
  rules
• Number must be greater than or equal to 50
• Number must be in increments of 25

63
Required, Allow Zero Length

• These are similar
• Required requires that data be entered
• Allow Zero Length applies to text and memo
  only, allows a blank field to be accepted

64
Indexed

• Speeds searches on frequently sorted or search
  fields
• Slows data entry, requires more memory
• Only use when needed
• Key fields are always indexed

65
Relationships between tables
66
Types of relationships

• One to many most common relationship
• Employee to Sales
• Each record in Sales matches only one record in
  Employee, but one employee had many sales
• Many to many strongly discouraged, unnecessary
  data redundancy
• Student to Classes
• Each record in one database matches multiple
  records in another
• Solved by creating and intermediary table with
  one-to-many relationships at each end
• Pull the Key field from each table together in
  third table is a simple way

67
Types of relationships (cont)

• One to one rare because it is two dimensional,
  can use Excel instead
• Business information to personal information
• Allows separation of databases, store personal
  information in a limited access format (password)

68
Viewing relationships

• Tools/Relationships
• Drag around boxes, size boxes, lines will move
• Common fields are linked by lines
• Key fields are in bold
• Key fields are often, not always, the linking
  field
• Infinity symbol means many, 1 means 1

69
Forming and editing relationships

• R-click on line to edit or delete relationship
• Click and drag one field to same field in
  different table to form relationship
• One and many icons dont show

70
Forming and editing relationships (cont)

• Click on line to edit properties
• Enforce referential integrity will turn them on
• Cascade delete will delete all records with
  references to that record BE CAREFUL
• Cascade update is not as dangerous will
  update all affected fields

71
Extra credit!

• Can you find the table created to link two
  many-to-many relationship tables together?

72
Queries
73
Two kinds of queries

• Select queries
• Allow you to select, view, and analyze data
• Action queries
• Allow you to modify data
• 4 kinds Update, Delete, Append, Make table

74
Creating a query

• Select the database
• Queries/New
• Design view
• Select needed tables from the show tables box
• Close the box
• Tables must be connected, or all permutations
  will show, and your PC will crash
• Intermediary table can be used

75
Select the fields

• Drag or D-click or pull down needed fields to the
  field boxes
• To add all fields, click asterisk or D-click the
  Table header

76
Specify sort order

• Not required, but good to find an easy way to
  look through the data
• Ascending or descending in selected field
• More than one field selected, will sort left to
  right
• You can sort with a hidden column to circumvent
  this

77
Specify criteria

• Limits results of the query
• Use same expressions as advanced filter
• Multiple criteria use and, or
• Horizontal lines are and
• Vertical lines are or

78
Expressions and calculated fields

• Bring up the tables you are working from
  (Queries/New/Design view)
• In Field grid, put field names in brackets
• Join or calculate fields with expressions
• R-click, choose build to build with buttons

79
Expression and calculation examples

• FirstName Last name
• City, State Zipcode
• UnitPriceQuantity
• UnitPrice.25
• Expr1 is default column name, type in your own to
  left of colon

80
Parameter Queries

• For queries you run frequently, with different
  values
• Design query normally, type prompt text between
  brackets
• Enter the last name, or Enter the date
• When someone brings up the query, it will prompt
  them for the variable

81
Save your query

• Use save button, or File/Save
• Use default name or name it yourself
• After save, it becomes part of the database, and
  will show when you hit the queries button in the
  database window