Designing Tables in Microsoft Access

1
Designing Tables in Microsoft Access

• By
• Ed Lance

2
About Me

• Independent Database Consultant
• Worked with Access since 1.0
• Many years designing, developing, tuning, and
  developing with databases.
• SQL Server and .Net development consulting
• http//SanDiegoDataPro.com

3
Topics

• Introduction
• Creating Tables
• Setting up Relationships

4
Introduction

• This presentation will cover designing tables and
  relationships in MS Access
• Not covering concepts much (i.e. relational DB
  theory I have slides on that).
• Not covering linking external tables.

5
IMPORTANT!

• Tables and relationships are the foundation of
  your Access application
• Its vitally important that you get them properly
  set up BEFORE you work on queries, forms or
  reports.
• Avoids road blocks and kludges
• Designing other objects goes faster in Access
• You need to have a working design, (normalized)
  before you begin.

6
Creating a Table

• Several ways to start
• Datasheet View Just start entering data.
• Design View Usual way, gives you all the
  properties
• Table Wizard Can save some time if you have
  common data, and help you learn
• Tables can get created other ways as well Make
  table queries, importing etc.

7
Table Design View

• Allows you to edit the structure of the table and
  set properties for fields (columns)
• Pic here

8
Adding Fields

• Enter Field Name, Data Type and Description
• Field Names can contain spaces
• Naming Conventions?
• Description is optional but is good for self
  documenting applications. (Watch out, shows up
  in status bar in forms!)
• Setting Primary Key

9
Data Types

• Following data types are available
• Text Character data up to 255 chars
• Memo Up to 64K chars
• Number Integer and Float types
• Date/Time Dates and Time values
• Currency Currency values without rounding loss
• Autonumber Long Int., Access manages
• Yes/No bit value.
• OLE Object Binary objects, e.g. Word, Excel
  docs
• HyperLink 64000 chars, treated as hyperlink

10
Field Properties

• Properties in the General Tab
• Field Size Number of chars or size of numeric
  type
• Format How are those number displayed
• Input Mask For data entry
• Caption Used by form designer
• Default Value If nothing entered in field
• Validation Rule/Text validation expressions
• Required Dont allow Null values
• Allow Zero Length Allow in text fields
• Indexed Is this field indexed
• Others Unicode, IME, Smart Tags

11
Lookup Tab

• Allows you to set the default control that
  displays the value.
• Shows up everywhere.
• Can be overridden in forms reports
• For text and number, can use a combo or list box.
• For Yes/No, can use checkbox or combo box

12
Indexes

• An Index is like a hidden lookup table on one or
  more fields.
• Can have a huge impact on Select queries
• Can slow down inserts updates
• Deciding what fields to index is an art.
• At least get Primary Key and foreign keys
• Good naming makes this automatic
• An index can be on more than one field.

13
Indexes

• Index dialog
• Index Name, Field Name, Sort Order
• Properties
• Primary Is field part of primary key
• Unique Does the index enforce unique values
• Ignore Nulls Is Null considered a value?
• Changing index is changing table design, must
  save table.

14
Relationships

• Very important!
• Setting relationships at design time on tables
  means Access is responsible for referential
  integrity, not you
• Also, relationships are recognized automatically
  when designing queries, forms, reports

15
Relationship Window

• Used to manage relationships and show
  graphically.
• Again, good naming helps you out.
• Window is also saves layout of itself, so it will
  ask you to save.

16
Relationship Window

• Double-click relationship line to get properties
• Relationships can be more than one field.
• Referential Integrity
• Its a good thing
• Cascading Updates. Not an issue if you always
  use Autonumber PKs
• Cascading Deletes. Useful but dangerous.

17
Reviewing the Table

• After you are done making tables, open them all
  up and check them out.
• Subdatasheets. Do we need these?
• Print relationship window for documentation.

18
Design Tips (IMHO)

• Get table design down before you rush into it.
• Name the PK field the table name w/o prefix
  (tblProducts ? ProductsID)
• Name foreign keys same as the PK they join to.
• Always use Autonumber PKs (this started an
  argument at a dev. convention)
• Dont name fields reserved words.
• Use appropriate data types.
• Set relationships on tables, enforce referential
  integrity.
• Create indexes, but not too many. Watch for ones
  Access creates that you didnt need.

19
Questions?