SILS iSchool

01 Nov 2018

Value Added | daily

Class Schedule

Basics | sessions 01-05

21 Aug | intro
23 Aug | clients
28 Aug | servers
30 Aug | networks
04 Sep | basics lab

Web Development | sessions 06-11

06 Sep | structural layer
11 Sep | presentational layer
18 Sep | working with layers
20 Sep | behavior layer
25 Sep | images & design
27 Sep | website lab

Document Markup | sessions 12-14

02 Oct | object layers
02 Oct | graphics
09 Oct | document markup lab

Spreadsheets | sessions 15-19

11 Oct | spreadsheets, formulas & functions
16 Oct | thoughts about data display
 18 Oct  | Fall Break 
23 Oct | database tools
25 Oct | spreadsheets lab

Relational Database | sessions 20-26

30 Oct | relational databases

01 Nov | tables | 05.01 | next session

06 Nov | relationships
08 Nov | input & output
13 Nov | SQL
15 Nov | complex queries
20 Nov | databases lab
 22 Nov | Thanksgiving 

Presentation | sessions 27-30

27 Nov | presentation design
29 Nov | presentation delivery
04 Dec | presentation lab
13 Dec | 0800-1100 | final in class presentation





Healthy, well-constructed tables are one of the foundation stones of an effective relational database.

Access Tables

[Microsoft Help]

Tables store data, so they're essential building blocks of any database

A database should have a separate table for every major component of the database

Data should not be duplicated in multiple tables

Duplicating data is a common error,
but it's easy to avoid if you structure your tables well

Each table contains records and fields in Access which are analogous to rows and columns in Excel.

Terminology Comparison
type ⇒⇒⇒⇒⇒⇒ ⇓⇓⇓⇓⇓⇓
ERModel entity set composed of entities which have attributes
Access tables composed of records which have fields
Excel worksheets composed of rows which have column header cells

But we can do so much more with field properties in Access than we could do with column headings in Excel.

back to top

Records

A record is a collection of facts about a particular person, event, or other item of interest

  • For example, in a library database, authors and their details might be a record in an Authors table
  • Books and their information might be a record in a Books table

Each row contains a record which is a single entry in a table and each record is composed of several descriptive fields.

back to top

The images below depict two different ways to view table components

design view

table design view

datasheet view

table datasheet view

Each column contains a field. A field is a single kind of fact that may apply to each person, event, or other record.
A column heading/field name is a label describing the type of information in the column/field.

a screenshot of the Access tables dialog box showing the field name

back to top

Creating Tables

When creating databases, sketch out the different tables you will need and the data contained in the tables before you begin

  • in earlier versions of Access, you used the Table Wizard to create a table quickly by answering a few questions
  • with the arrival of Access 2007, the Table Wizard has been replaced by table and field templates
  • you can create and modify tables and fields while working in Datasheet view
Access 2013 create a table

back to top

Entering, or importing, data, by using existing data

You can import data from an Excel workbook (for example) by using the External Data tool which allows you to find the needed file type.

get external data

This will start the import wizard that permits you to select the worksheets and the columns that you want to import.

external data wizard

The import wizard is very sensitive and often will not import data directly into an existing table because there may be an unseen formatting difference between the data in Excel and how it appears in Access

  • to get around this problem, set up your import data to look exactly like an existing table, but have the Wizard create a new table
  • once the data imports into a new table, you can modify the new table so that it is an exact copy of the existing table
  • once this is done, delete the original table and rename the new table with the name of the deleted table

You will need to set up your Excel table header rows to match your Access table fields

  • after you have imported the data to a new table, you will find that Access will not allow you to change the ID field from a number to an Autonumber
  • to get around this, you can create a new ID field that uses autonumbers and make the new field the primary key
  • once you have done this, you may delete the old ID field and the new autonumber ID field will create new ID numbers for each record

back to top

table fields

Table Fields

Once you have created a new table, view the new table in design view

  1. if an existing field name isn't descriptive enough, you can rename the field
  2. a field's data type limits and describes the kind of information you can enter in a field, such as Number or Currency
  3. you use a unique identifier, called a primary key, for each record in your table
  4. field properties are a set of characteristics that provide additional control over the data.

back to top

Field data types

data type reference

AutoNumber:

Automatically increments; used for primary key (unique identifier)

auto number reference

Number:

Numbers are integers that are negative or positive
not numbers that do not have numeric values like SSANs or PIDs

number data type reference

Text:

Used for words or non-value numbers; default setting; 255 characters max

Currency:

Dollar or other currency amounts, with choice of decimal places

Date/Time:

Dates & times. Like Excel, Access stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day. Formatting is necessary to have the numbers display as dates and times.

Lookup Wizard:

for Lookup tables

  • this will be very important to learn because it allows you to enter values in a table by finding the data in another table
  • the Wizard will create a tool through which you will enter foreign key data, to relate tables together
lookup wizard lookup wizard dialog box

back to top

Setting Primary Keys

To distinguish one record from another, tables contain a primary key field

the primary key is an identifier - such as a part number, a product code, or an employee ID - that is unique to each record

The primary key should be a piece of information that won't change frequently

  • if you want, Access can assign a numeric primary key that increases by 1 each time you add a record to a table
  • this number continues to be associated with this record, even if you add and delete other records entered before this record in your database
primary key

In Design View, click the "Primary Key" icon in the desired field.

back to top

Field Properties

all field types except AutoNumber and OLE Object have the following properties

  • Some properties are primarily for text fields
  • If you want to see a definition of Field Properties, hit F1
data type help dialog box

Field Size:

number of characters, max is 255 (except for Memo)

Note the distinction between Long Integer (storage requirement is four bytes) and Double (storage requirement is eight bytes) for a number field
number field types

Format:

how the field's contents will be displayed

Decimal Places:

option appears when data type is numeric

Input Mask:

limits formats that can be entered (zip code, phone number, etc.)

input mask

Should it be stored in the database in a specified format?

  • Use the wizard by highlighting the field and click on the expression builder button (...) which starts the Wizard and steps you through the process of creating a specified way to display data.
input mask dialog box

Caption:

label that will be attached to the field on a form or report. Can be different than the actual field name

Default Value:

default for new entries

Validation Rule:

a range of acceptable entries; checks values

Validation Text:

appears in status bar when field is selected

Required:

a yes/no field that indicates if a value is mandatory

Allow Zero Length:

a yes/no type setting that indicates whether a text string with no length ("") is valid

Indexed:

creates an index for the field; improves searching & sorting

  • primary keys are always indexed (No Duplicates)
  • index fields that are frequently used
  • Unicode Compression: improves performance
indexed fields

You can edit in either Design View or Datasheet view, however, the only place to make permanent changes is in Design View.

back to top

Entering/Deleting/Sorting Records

  • added record automatically saved when you move to a new record
  • hit "Enter" or "Tab" on the keyboard to move to the next field when entering data
  • "Delete Record" button on toolbar
  • "Sort" buttons on toolbar

back to top

Functions

As in Excel, you can use functions in Access. For example, you can use functions in forms, reports, the design of tables, etc.

Functions have the same format as in Excel

  • use "Help" to find out about different functions
  • in the Help index, "functions, reference topics" has a list of all of the built-in functions

Also, you can use the expression builder to build a function. For example,

=Date()

generates the current date. [You need to include the parentheses, ().]

=Date()+30

generates a date that is 30 days from the current date

back to top