IG009F15A

From CourseWiki

(Redirected from IG009)
Jump to: navigation, search



IG009F15A Microsoft Access

Contents

Overview

Access 2013 icon Learn your database fundamentals with Microsoft Access! You have your data and want to use the computer to organize it in ways that will allow you to capture, manipulate and analyze it easily.

Course Description

Microsoft Access allows you to capture, manipulate and analyze data easily. Learn the fundamental concepts, terminology, objects and tools that make up a database. Design and create tables, queries, forms and reports, and get an introduction to the standard language of databases: SQL.

Duration: 12 hours

Register for this course: Access® - Introduction | Following course: Register for Access® Troubleshooting & Designing Access Databases

[edit] Theoretical

  • Data Normalization
  • Database terminology
  • Tables
  • Relationships
  • Access 2010 / 2007 User Interface
  • Queries
  • Forms
  • Reports

[edit] Practical

  • General
    • Create Access databases
    • Open database objects in multiple views
    • Move among records
    • Format datasheets
  • Creating and Modifying Tables
    • Add a predefined input mask to a field
    • Create Lookup fields
    • Modify field properties
  • Creating and Modifying Queries
    • SELECT queries
      • Multiple Tables
      • Calculated expressions
  • Creating and Modifying Forms
    • Create/Display Forms
    • Modify Form properties
  • Viewing and Organizing Information
    • Enter, edit, and delete records
    • Create queries
    • Sort records
    • Filter records
  • Defining Relationships
    • Create one-to-many relationships
    • Enforce referential integrity
  • Producing Reports
    • Create and format reports
    • Add calculated controls to reports
  • Preview and print reports
  • Integrating with Other Applications
    • Import data to Access
    • Export data from Access

Prerequisites

Please note: We will be using Access 2010 during this course. If you are using Access 2003 or earlier, you will find that the principles will be applicable, but the user interface will be different.

Some experience with Microsoft Windows and a mouse.

Experience with Excel will be an asset but is not required.

Computer Equipment

The sessions are conducted in a computer lab, and you will have a computer provided for your use. You are welcome to bring your own computer to the lab sessions if you prefer to use that. Wireless access is available in the lab.

Please note that at times you will be doing a fair bit of typing and you will want to have a large enough screen and separate physical keyboard so that you can view content and type at the same time.

If it appears that you are having challenges with your own device, we will ask you to use the computer provided for you in the lab.

Storage

Please plan to use your own cloud based storage SkyDrive, DropBox, iCloud (or similar) or a USB storage key.

Instructional Methods

  • Hands-on labs
  • Discussion
  • Lecture
  • Online collaboration

Course Materials

  • Web site
  • Course notes
  • Printed copies of on-screen lecture notes available on request during lectures

No Recording or Pictures in the Lab

Please note: Taking pictures or video during the course sessions is not permitted. All the course materials and lecture notes are available in physical form and on the web.


Register for this course: Access® - Introduction | Following course: Register for Access® Troubleshooting & Designing Access Databases

  • Current Session IG009F15A
    • 2 Tuesdays: November 3 – 10, 2015 : 9:00am – 04:00pm
    • Lab C460
    • CA: Mandy Lui
    • Students Registered: 6 *
  • Examples, Samples and Notes on SkyDrive

Sample files



Follow JimUBC on Twitter for latest course updates
Follow JimUBC on Twitter for latest course updates

Coming Soon
Courses coming


This site:

qr this site



Goals

Day One

By the end of the first day you should be able to read and understand Database Design Basics — Microsoft

Morning

  • Database fundamentals
    • Why we need databases
    • Basic concepts and terminology
      • Physical Storage
      • Primary Keys, Foreign Keys
      • Normalization

Afternoon

  • Introduction to the Access User Interface
  • Tables and data table maintenance (Add/Change/Delete) also known as CRUD (CReate Updated Delete)
  • Conceptual: Dividing data into tables
  • Relationships:
    • One to Many
    • Referential Integrity
    • Many to Many
  • Creating Queries
  • Forms - introduction to creating and modifying
Follow up Reading

Introduction to Tables — Microsoft (We are only interested in desktop databases. You can skip references to web apps and SharePoint)

How to create a SELECT query— Microsoft

Database Design Basics — Microsoft


Day Two

By the end of the second day you should be able to create a desktop database in Access. Specifically, you should be able to create:

  • Tables
  • Relationships
  • Queries
  • Forms
  • Reports

Morning

Reference: Introduction to Tables— Microsoft

  • Creating Tables in an Existing Database
    • Importing Data from Excel
  • Creating Desktop Databases
    • Blank Database
    • From Templates

Afternoon

  • Queries - continued
  • Forms
  • Reports

Access Specifications

Access 2013

  • Maximums (same as Access 2010/2007)
    • Database size 2 gigabytes
    • Table size 2 gigabytes
    • Characters in a record 4000
    • Fields in a Table 255
    • Characters in a text field 255
    • Characters in a memo field 65,536 (or 2 gigabytes if written by a program)

Access 2013

Overview

The Ribbon

Backstage View

Navigation Pane

Image:Access2013NavigationPane.png

Tabbed Documents

Image:Access2013Tabs.png

Show or hide document tabs

  1. Click the File tab, and then click Options.
    The Access Options dialog box appears.
  2. In the left pane, click Current Database.
  3. In the Application Options section, under Document Window Options, select Tabbed Documents.
  4. Select or clear the Display Document Tabs check box. Clearing the check box turns off document tabs.
  5. Click OK.

Galleries

Same as Access 2010

Access 2013 General Info

Specifications
  • Maximums (same as Access 2010/2007)
    • Database size 2 gigabytes
    • Table size 2 gigabytes
    • Characters in a record 4000
    • Fields in a Table 255
    • Characters in a text field 255
    • Characters in a memo field 65,536 (or 2 gigabytes if written by a program)
File Formats
  • Prior to Office 2007 *.mdb format
  • Office 2007 *.accdb
    • new
      • multi-valued fields
      • integration with SharePoint
    • no more
      • user level security
      • replication

Which file format should I use in Access 2007?



Access 2010

Access 2010 User Interface

Overview

Image:Access 2010 Ribbon.png

The Ribbon

Image:Access 2010 Ribbon1.png

Backstage View

Image:Access 2010 Backstage View.png

Navigation Pane

Image:Access2010NavigationPane.jpg


Tabbed Documents

Image:Access2010Tabs.jpg

Show or hide document tabs

  1. Click the File tab, and then click Options.
    The Access Options dialog box appears.
  2. In the left pane, click Current Database.
  3. In the Application Options section, under Document Window Options, select Tabbed Documents.
  4. Select or clear the Display Document Tabs check box. Clearing the check box turns off document tabs.
  5. Click OK.

Galleries

Access 2010 General Info


Specifications
  • Maximums (same as Access 2007)
    • Database size 2 gigabytes
    • Table size 2 gigabytes
    • Characters in a record 4000
    • Fields in a Table 255
    • Characters in a text field 255
    • Characters in a memo field 65,536 (or 2 gigabytes if written by a program)
File Formats
  • Prior to Office 2007 *.mdb format
  • Office 2007 *.accdb
    • new
      • multi-valued fields
      • integration with SharePoint
    • no more
      • user level security
      • replication

Which file format should I use in Access 2007?



Access 2007

Access 2007 User Interface

The Ribbon

Image:Access 2007 Ribbon.png

Specification

  • Maximums
    • Database size 2 gigabytes
    • Table size 2 gigabytes
    • Characters in a record 4000
    • Fields in a Table 255
    • Characters in a text field 255
    • Characters in a memo field 65,536 (or 2 gigabytes if written by a program)

Source: — Access 2007 Specifications

File Formats

  • Prior to Office 2007 *.mdb format
  • Office 2007 *.accdb
    • new
      • multi-valued fields
      • integration with SharePoint
    • no more
      • user level security
      • replication

Which file format should I use in Access 2007?


Reference: Guide to the Access 2007 user interface — Microsoft

Access Objects

Table

Image:Access Table View 01.png


Selecting Views of Objects

Image:Access Selecting Views.png


Table Design View

Image:Access Table Design View 01.png

Details of Fields

Image:Access Table Design View 02.png

Pivot Table View

Image:Access Pivot Table View 01.png

Relationships

Image:Access 2007 Relationships 01.png

Demo: Creating a Relationship between two tables


Many to Many Relationships

Many to Many Relationships

Resources

Query Design

Image:Access Query Design 01.png

Telephone List

Create | Query Design

Image:Access2010QueryDesign00.png

Select the Table

Image:Access2010QueryDesign01.png

Select the Columns to be displayed

Image:Access2010QueryDesign02.png

Set Sort options

Image:Access2010QueryDesign03.png

Click the Datasheet View

Image:Access2010QueryDesign04.png

See the Results

Image:Access2010QueryDesign05.png

Add a Filter

Filter by City ( City=Boston )

  1. Add the City field
    Image:Access Query Design 07.png
  2. Add a Criteria Boston
    Image:Access Query Design 08.png
  3. View the result
    Image:Access Query Design 09.png

Parameter Query

  1. Set up the Parameter Query but putting a prompt between square brackets [What City]
    Image:Access Query Design 10.png
  2. View the result
    Image:Access Query Design 11.png

Query with Two Tables

  1. Right Click in the upper Pane and choose Show Table
    Image:Access Query Design 21.png
  2. Add the Team table
    Image:Access Query Design 22.png
  3. Notice that Access joins the Team Table to the People Table based on the Team field
    Image:Access Query Design 23.png
  4. Add the Team Name field to the query
    Image:Access Query Design 24.png
  5. Vuew the result
    Image:Access Query Design 25.png

Query with an Expression

Copy/Paste

 Age: Int((Now()-Birthdate)/365.24)

Image:Access Query Design 31.png

Image:Access Query Design 32.png

Class notes

Image:Cds.png Image:Ig0009s07aa.png

Media:Cds.tif

Image:accesswindup.png

Many to Many Relationships

Many to Many Relationships

Questions and Issues

IG009F15A Questions


Copy / Paste Code for Lab Session

Age

 Age: Int((Now()-Birthdate)/365.24)

or

 Age: DateDiff("yyyy", [Birthdate], 
 Now())+ Int( Format(now(), "mmdd") < Format([Birthdate], "mmdd") )

Month

This expression displays the month of a date

 Month: Format([BirthDate],"mmm")

First or Salutation

This expression fills in the Title if there is no FirstName. Note: This requires that both the People table and the Titles table are part of the query.

First: IIf([People].[FirstName]<>"",[People].[FirstName],[Title].[Description])

Validation for Email

You will have to add a line for each other total level domain (e.g. edu)

Like "[A-Z]*@[A-Z]*.com" 
Or Like "[A-Z]*@[A-Z]*.net" 
Or Like "[A-Z]*@[A-Z]*.org" 
Or Like "[A-Z]*@[A-Z]*.ca" 
Or Like "[A-Z]*@[A-Z]*.uk" 
Or Like "[A-Z]*@[A-Z]*.edu"

Alternative

Not as precise but will catch common errors.

((Like "*?@?*.?*") And (Not Like "*[ ,;]*"))

Create a Table

2010 Application Parts

  1. Create
  2. Application Parts
  3. Quick Start - pick a table to create

Image:Access Quick Parts 01.png

If you choose Contacts as an example, Access creates forms for you. Here is an example.

Image:Access Quick Parts 02.png

Video: Add features to an Access database by using Application Parts

Application Parts replace Table Templates - accessjunkie.com

2007 from a Template

Image:Access 2007 Create Table from Template01.png

Here is new table (in Table Design View). Notes:

  • You will want to change the name of the Primary Key to TableNameID
  • Then review the fields in the table making changes as necessary


Image:Access 2007 Create Table from Template02.png

Table Design

For discussion of field types to consider when creating a table.

Image:Table Design001.png

Input Masks and Validations

Telephone

Telephone (size 12)

!99" ("000") "000\-0000;;_

Dates

Format: yyyy/mm/dd

Input Mask: 0000-99-99;;_

Validation Rule: Not within last year

< Now()-364

Validation Rule: today or earlier

<=Now()

Email

Email:

Like "[A-Z]*@[A-Z]*.com" 
Or Like "[A-Z]*@[A-Z]*.net" 
Or Like "[A-Z]*@[A-Z]*.org" 
Or Like "[A-Z]*@[A-Z]*.ca" 
Or Like "[A-Z]*@[A-Z]*.uk" 
Or Like "[A-Z]*@[A-Z]*.edu"


Email2:

((Like "*?@?*.?*") And (Not Like "*[ ,;]*"))

Reference: Control Data Entry with Input Masks

Access 2007 Attachments

Access 2007 Attachments tutorial

Create a Database from a Template

Image:Access 2010 Create Database01.png


In this example we will choose a template on microsoft.com using the search word "contacts".

Image:Access 2010 Create Database02.png

After we create the database, review the documentation and then close the documentation screen.

Image:Access 2010 Create Database03.png

Explore your new database.

Image:Access 2010 Create Database04.png

Access 2007 Create Database from a Template

Import Data from Access

Create New Blank Database

Image:ReferentialIntegrityDemo0201.png

External Data

On the External Data Tab choose Access

Image:ReferentialIntegrityDemo0202.png

Get External Data Wizard

Image:ReferentialIntegrityDemo0203.png

Open the Database that has the data you want to import

Image:ReferentialIntegrityDemo0204.png

Confirm that you want to Import not link to the data

Image:ReferentialIntegrityDemo0205.png

Select the objects to import

Image:ReferentialIntegrityDemo0206.png

New database with the imported tables

Image:ReferentialIntegrityDemo0207a.png

Close the empty table

Image:ReferentialIntegrityDemo0208.png

Create Relationships

Image:ReferentialIntegrityDemo0209.png

Foreign Key Relationships

Image:ReferentialIntegrityDemo0210.png



Referential Integrity

Create New Blank Database

Image:ReferentialIntegrityDemo0201.png

External Data

On the External Data Tab choose Access

Image:ReferentialIntegrityDemo0202.png

Get External Data Wizard

Image:ReferentialIntegrityDemo0203.png

Open the Database that has the data you want to import

Image:ReferentialIntegrityDemo0204.png

Confirm that you want to Import not link to the data

Image:ReferentialIntegrityDemo0205.png

Select the objects to import

Image:ReferentialIntegrityDemo0206.png

New database with the imported tables

Image:ReferentialIntegrityDemo0207a.png

Close the empty table

Image:ReferentialIntegrityDemo0208.png

Create Relationships

Image:ReferentialIntegrityDemo0209.png

Foreign Key Relationships

Image:ReferentialIntegrityDemo0210.png


Edit Relationships

Image:ReferentialIntegrityDemo0211.png

Referential Integrity Provstate

Image:ReferentialIntegrityDemo0212.png

Referential Integrity Country

Image:ReferentialIntegrityDemo0213.png

Notice 1:Many Join

Image:ReferentialIntegrityDemo0214.png

Exit the Relationships Window

Image:ReferentialIntegrityDemo0215.png

View the People Table

Image:ReferentialIntegrityDemo0216a.png

Notice the Drop Down list for Provstate

Image:ReferentialIntegrityDemo0217a.png

Cascade Updates example

Existing Data "BC"

Image:ReferentialIntegrityDemo0218.png

Close People table

Image:ReferentialIntegrityDemo0219.png

Provstate table change the data

Image:ReferentialIntegrityDemo0220a.png

Provstate table change the key

Image:ReferentialIntegrityDemo0221.png


View the People table

Image:ReferentialIntegrityDemo0222.png



Referential Integrity

Forms

Access 2010

When you create a new form by using one of the form tools on the Create tab, or when you add fields to a form while it is open in Layout view, Access 2007 puts text boxes and other controls in guides called layouts. A layout, indicated by an orange grid around the controls, helps you align controls horizontally and vertically to give the form a uniform appearance. The following illustration shows a "stacked" layout on a form that is open in Layout view

Starting with a datasheet view of a table

Image:Access2010Form01.jpg

Create Tab

Image:Access2010Form02.jpg

Select Design View

Image:Access2010Form03.jpg

Manipulate specific objects

Image:Access2010Form04.jpg


Access 2007

Image:Access Form Layout 01.png

Source: Move and Resize Controls Independently — Microsoft.

Image:AccessFormRemoveGroups.jpg

Design View

Image:Access Form Design 01.png


Forms from Two Tables

This allows you to have a form and subform if you have a one to many relationship between two tables. In this example we use Team and People.

Here is the finished result.

Image:Access 2007 Create Form from two tables00.png


  1. To create this - start on the Create Tab.

    Image:Access 2007 Create Form from two tables01.png
  2. Pick the first Table: Team, then pick the Team Name field

    Image:Access 2007 Create Form from two tables02.png
  3. Pick the second Table: People. Then pick fields: LastName, FirstName, Score

    Image:Access 2007 Create Form from two tables03.png
  4. View the Data by Team

    Image:Access 2007 Create Form from two tables04.png
  5. Pick a Layout - in this case Datasheet (try Tabular later).

    Image:Access 2007 Create Form from two tables05.png
  6. Pick a Style

    Image:Access 2007 Create Form from two tables06.png
  7. Set the Form and Subform Titles (you probably don't need to change these).

    Image:Access 2007 Create Form from two tables07.png

That's it! You are done. But you can still go into Design view and make changes if you like for sorting in the Subform.

Image:Access 2007 Create Form from two tables00.png


Country / Province form

Verify that there is a 1:Many relationship between Country and ProvState

Image:FormCountryProvState000.jpg

1 Create Tab | Form Wizard

Image:FormCountryProvState001.jpg

2 Select the Country table, and select all fields

Image:FormCountryProvState002.jpg

3 and select all fields

Image:FormCountryProvState003.jpg

4 Select the ProvState table,

Image:FormCountryProvState004.jpg

5 Add Provstate and provinceorstate

Image:FormCountryProvState005.jpg

6 View the data by Country (group by Country)

Image:FormCountryProvState006.jpg

7 Set the subform view to Datasheet

Image:FormCountryProvState007.jpg

8 Accept the default names

Image:FormCountryProvState008.jpg

9 This is the Form View

Image:FormCountryProvState009.jpg

10 Use ctrl-F to find CA (Canada)

Image:FormCountryProvState010.jpg

11 Here is the form with the CA record selected

Image:FormCountryProvState011.jpg

12 Switch to the Design view and if necessary use the Form Design | Arrange Tab.

  • Select all fields in the Detail band (click, ctrl-a)
  • Remove the Layout.

Image:FormCountryProvState012.jpg

13 Design View - move fields around like this.

Image:FormCountryProvState013.jpg

14 Form View - check out the new layout of the fields

Image:FormCountryProvState014.jpg

15 Design View: ProvState... add Capital

Image:FormCountryProvState015.jpg

16 View the new field

Image:FormCountryProvState016.jpg

17 Populate the new column with the capitals of the Provinces

Image:FormCountryProvState017.jpg

18 Form View - see the data in the normal Form View.

Image:FormCountryProvState018.jpg



Reports

Access 2010

Access Report with Grouping and Sorting

General Principle

Use the Report Layout or Report Design View. Click the Group & Sort Command and then set options at the bottom of the screen.

Image:Access Report Group Sort.png

Team People

In this video we create a report with Team Names, and people on that show along with their bowling scores and the Team average bowling score.


Show People

In this video we create a report with Show Names, and people on that show along with their bowling scores and the Show average bowling score.


Tutorial Group and Sort Data in a Report — Microsoft


Access 2007

Report from Two Tables

This example is almost identical to what you did with the Form above, drawing data from two tables: Team and People.

Here is the finished result:

Image:Access 2007 Create Report from two tables00.png

  1. On the Create Tab, Click Report Wizard. Then choose the Table:Team and the field Team Name, Coach, Captain.

    Image:Access 2007 Create Report from two tables01.png
  2. Change to Table:People and add Score, LastName and FirstName

    Image:Access 2007 Create Report from two tables02.png
  3. View the data by Team

    Image:Access 2007 Create Report from two tables03.png
  4. We don't need more groupings levels

    Image:Access 2007 Create Report from two tables04.png
  5. Add sorting for the detail records

    Image:Access 2007 Create Report from two tables05.png
  6. Add the Summary Options

    Image:Access 2007 Create Report from two tables06.png
  7. You are back at the Sorting Screen - no more changes here

    Image:Access 2007 Create Report from two tables07.png
  8. We don't need to make any changes to the layout

    Image:Access 2007 Create Report from two tables08.png
  9. Make changes to the style if you want

    Image:Access 2007 Create Report from two tables09.png
  10. All Done

    Image:Access 2007 Create Report from two tables10.png

Finished!

Image:Access 2007 Create Report from two tables00.png

Import External Data from Excel



  1. Image:Access Import from Excel01.png


  2. Image:Access Import from Excel02.png


  3. Image:Access Import from Excel03.png


  4. Image:Access Import from Excel04.png


  5. Image:Access Import from Excel05.png


  6. Image:Access Import from Excel06.png


Resources

Review and Follow Up Reading

Tutorials

Access 2013

Access 2010

Image:Access 2010 Ribbon.png


What's New in Access 2010 — Microsoft

What's New, Changed, Removed, Improved in Access 2010 — Microsoft


Access 2010 Specifications

What's New in Microsoft Access 2007

What's New in Microsoft Access 2007

Access 2003

Session Specific Notes

IG009F15A Notes


Personal tools