Introduction to Access

1
Introduction to Access

• A Relational Database Management System

2
Contents

• Different Tasks Software
• What is a Database?
• MS-Access Objects
• Table Design
• Data Types

• Field Properties
• Field Sizes
• Input Masks
• Validation Rules
• Primary Key Fields

3
Different task - different software

• Wordprocessors
• For writing reports, letters and memos
• Spreadsheets
• For budgets, cash-flow projections, modelling.
  Also graphs
• Databases
• For organising complex data, sorting, selecting
  and tabulating results
• Graphics packages
• Either for graphs and charts, or for diagrams
  (or both)
• Statistics packages
• Describe and analyse relationships in data.

4
Example packages

• Wordprocessors - Word, WordPerfect
• Spreadsheets - Lotus 1-2-3, Excel
• Databases - dBASE, Access, Oracle
• Graphics - Harvard Graphics, Freelance
• Statistics - Minitab, INSTAT, SAS, SPSS, GENSTAT

5
What is a Database?

• A collection of information related to a
  particular subject or purpose
• MS-Access is a Relational Database Management
  System
• With Access you build relational databases that
  store related information in one place

6
MS-Access Objects

• A database in Access is a single file
• The database file contains up to 7 different
  types of object

Tables Queries Forms Reports
Pages Macros Modules
7
Tables

• Tables store data
• Each table is a collection of data about a
  particular subject
• Data is organised into rows (records) and columns
  (fields)

8
Queries

• Queries gather data you request from one or more
  tables
• With queries you ask questions about the data
• Each time the query is run the latest information
  is taken from the underlying table or tables

9
Forms

• Forms display data from tables or queries
• Forms are used to view, edit, or enter data,
  record by record
• Forms can be designed to show only the data you
  want to see, in the way you want to see it

10
Reports

• Reports summarise and present data from tables
  and queries
• Records can be grouped into many levels and
  summaries calculated for the different groups
• Each time a report is run it will use the latest
  data from the underlying table or query

11
Macros

• Macros automate the database by performing
  actions you specify
• Each macro is a set of actions which perform
  tasks such as opening a form or printing a report

12
Modules

• Modules are units of code written in VBA (Visual
  Basic for Applications)
• They are more powerful than macros, but also more
  complex to write
• VBA is also used to write Event Procedures

13
Objects in Access
14
The Database Window
15
Table Design
16
Data Types

• Text Alphanumeric characters
• Number Numeric values
• Date/Time Dates Times
• Yes/No Boolean values
• Currency Currency values
• Memo Lengthy notes
• OLE Object Pictures etc.

17
Field Properties

• As well as a defined data type, each field has a
  set of properties associated with it
• Field properties specify how you want the data
  handled, stored and displayed
• They allow you to set the field size, the format,
  validation rules, etc.
• The properties you can set are determined by the
  data type

18
Field Size

• The field size property is available for text and
  number fields only
• Text fields have a maximum field size of 255 the
  field size determines the bytes used to store the
  values
• For number fields the field size determines the
  range of values that can be stored

19
Input Masks

• An input mask is a format you specify to assist
  data entry
• It can restrict the kind of data that can be
  entered
• It also specifies how many characters are allowed

20
Input mask examples

• An input mask of ????? allows you to enter up to
  5 alphabetic characters
• An input mask of LLLLL forces you to enter 5
  alphabetic characters
• With either of the above you would only be able
  to enter 5 characters even if the field size was
  greater than 5

21
Warning!

• Access allows you to set a field size and an
  input mask which are not consistent.
• For example-
• you are able to set a field size of 3 and an
  input mask of LLLLL
• you can define an input mask for a number field
  using L and ? as mask characters - these only
  allow alphabetic characters on input

22
Validation Rules

• With this property you specify requirements for
  data entered into the field
• Validation rules are entered as expressions
• Examples
• lt100 Value must be less than 100
• UK or USA Text value must be either UK or USA
• Like C Value must begin with C

23
Primary Key Field

• The primary key is a field or set of fields that
  uniquely identifies each record
• Duplicates or null values are not allowed in the
  primary key
• If there is no obvious primary key you can use an
  autonumber field