From CourseWiki

(Redirected from IG047)
Jump to: navigation, search

Microsoft Access Level II - Troubleshooting & Designing Databases


Course Description

Starting with data in various forms including ASCII text files, Excel spreadsheets, and existing Access databases, you will examine typical problems that arise when dealing with data that is not appropriately formatted. You will examine advanced topics relating to table maintenance, queries, forms, reports, relationships between tables, and exports to different file types. Along the way, we will address issues and problems that are relevant to more experienced Access users (SELECT, UPDATE, DELETE, queries and Macros for more multi-step procedures).

Duration: 12 hours

Register for this course

For announcements please check the links to Twitter and Facebook at the right side of this page.



  • Considering Client/Server modes in Access
  • Rethinking your database design
  • Better
    • Tables
    • Queries
    • Forms
    • Reports


  • Table Design
  • Query Design
  • Form Design
  • Report Design
  • Importing and Linking to External Data
  • Dealing with
    • duplicate records
    • orphaned records
    • compound data in columns
    • archiving old records
  • Macros in Access - an introduction



We will be relying on material covered in the prerequisite course so you must have completed the following: Access: Introduction


A minimum of 6 months experience with Access is highly recommended.

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.


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.

Most Important Question

Access: Designing and Troubleshooting Access Databases

Coming Soon
Courses coming

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


Register for this course

  • Current Session IG047F13A
  • 2 Tuesdays - November 5 – 12, 2013
  • 9:00am - 04:00pm
  • Lab Lab C460
  • CA: Mandy Lui
  • Students Registered: 11 #
  • Examples, Samples and Notes on SkyDrive

Sample FilesNotes 2010Notes 2007-2003

right-click, then download

right-click, then download

Course Overview MindMap

live mind map

IG047F13A Questions

Using Office 365 to create an online database in minutes.

— edits: IG047F13A Questions

Access 2010 User Interface


Image:Access 2010 Ribbon.png

The Ribbon

Image:Access 2010 Ribbon1.png

Backstage View

Image:Access 2010 Backstage View.png

Navigation Pane


Tabbed Documents


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.


Access 2010 General Info

  • 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 2013 User Interface

Image:Access 2013 Home Tab.png

Image:Access 2013 Back Stage View.png


Create Subdatasheet

To create a subdatasheet, click the plus sign beside a row.


Remove a Subdatasheet

In Datasheet view, open the table or query that contains the subdatasheet that you want to remove.

On the Records menu, click More, point to Subdatasheet, and then click Remove.


Split Forms

A split form gives you two views of your data at the same time — a Form view and a Datasheet view. The two views are connected to the same data source and are synchronized with each other at all times. Selecting a field in one part of the form selects the same field in the other part of the form. You can add, edit, or delete data from either part (provided the record source is updateable and you have not configured the form to prevent these actions).


You can modify the properties that are specifically related to the Split form here:


Reference: Create a Split Form

Object Dependencies


Calculated Fields in Tables

In Access 2010, you can create table fields that calculate values. The calculations can include values from fields in the same table as well as built-in Access functions.


  • The calculation cannot include fields from other tables or queries.
  • The results of the calculation are read-only.

Reference: Calculated fields

Example in the section on Expressions

Image:Access2010 Calculated Field.png

Questions & Answers Specific to this Session

IG047F13A Notes

Client / Server Back End

Live Mind Map for dicussion of Client Server concepts with Access

Split the Database

Compile the Database

Reference Note: Split an Access Database — Microsoft

  • Note the instructions on how to compile the database are incorrect. See the image above on how to Make ACCDE.

Security in Access 2010


Introduction to Access 2010 Security - Discussion

Access and user-level security

Access does not support user-level security for databases that are created in the new file format (.accdb and .accde files). However, if you open a database from an earlier version of Access in Access 2010 and that database has user-level security applied, those settings will still function.
Important Permissions created by using the user-level security feature do not protect your database from users who have malicious intent, and are not intended as a security barrier. It is appropriate to use this feature to improve the usability of a database for trusted users. To help keep your data secure, allow only trusted users to access your database file or associated user-level security files by using Windows file system permissions.
If you convert a database from an earlier version of Access with user-level security to the new file format, Access strips out all security settings automatically, and the rules for securing an .accdb or .accde file apply.
Finally, remember that all users can see all database objects at all times when you open databases that have the new file format.

Trust Center

The Trust Center is a dialog box that provides a single location for setting and changing security settings for Access. You use the Trust Center to create or change trusted locations and to set security options for Access. Those settings affect how new and existing databases behave when they are opened in that instance of Access. The Trust Center also contains logic for evaluating the components in a database and for determining whether the database is safe to open or whether the Trust Center should disable the database and let you decide to enable it.
  1. File
  2. Options
  3. Trust Center
  4. Trust Center Settings
  5. Trusted Locations
  6. Add new location

Image:Access2013 Trusted Location.png

Sample Database: People

Relationships Window


Query Design

Create | Query Design


Select the Table


Select the Columns to be displayed


Set Sort options


Click the Datasheet View


See the Results




Introduction to Expressions

Code Snippets

Excerpts to Copy and Paste

Calculated Field in Table

Returns the long form of the day of week for the day on which a person was born


Reference: Calculated Fields. Note - valid only for Access 2007 and later.

Query Column Expressions

Age and Dates


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

- or -

Age: Int((Now()-[People]![Birthdate])/365.24)

Using DateDiff

This is all one line

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

Month mmm

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

Month to Sort By

MonthNo: Month([Birthdate])

Day of the Month

DayNo: Day([Birthdate])

Parameter Query Expressions

Use either of these in a column that has the Month as "mmm"


[What Month]

Input Box with Default (Mon)

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Eval("InputBox(""Birthdays for what month?"",""Month"",format(now(),""mmm""))")

Input Box with Default (City)

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Eval("InputBox(""What City?"",""City"",""Vancouver"")")

CrossTab Query Example

Expression for Year

Year: Format([Date],"yyyy")

Using IF

If you have a query using the People table and the Titles Table, and Fields: FirstName, LastName and Description this expression for Salutation uses Title if there is no FirstName.

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

Code Splitting

NewDept: Left([code2split],4)
NewBldg: Mid([code2split],5,3)
NewRoom: Right([code2split],3)

Name Splitting

SpacePos: InStr([FullName]," ")
First: Left([FullName],[SpacePos]-1)
Last: Right([FullName],Len([FullName])-[SpacePos])

Conditional Processing

Ratio: IIf( [Gender]="Male",

CrossTab Query

See the example in your notes (page 48) totalling cheques from the cheque register grouped by person. and year.

Access CrossTab Query Average Scores

Function in Modules

Splitting Fields (Names)

Public Function FirstField(SplitMe)
    Dim temp
    temp = Split(SplitMe, " ")
    FirstField = temp(0)
End Function
Public Function LastField(SplitMe)
    Dim temp
    temp = Split(SplitMe, " ")
    LastField = temp(UBound(temp))
End Function
Public Function FindInitial(SplitMe)
' find a dot in SplitMe and return the character
' that precedes it
    Dim DotPosition
    DotPosition = InStr(1, SplitMe, ".")
    If DotPosition > 0 Then
    FindInitial = Mid(SplitMe, DotPosition - 1, 1)
    End If
End Function

Age / Date

Another one you can try with Birthdates

Public Function calcAge(dtBirthDate) As Integer
' function to return age in years, given a Date of Birth
  calcAge = Int((Now() - dtBirthDate) / 365.24)
End Function

Expressions in Forms

Example showing Names and age

[FirstName] & " " & [LastName] & " is " & [txtAge] & " years old"

Calling a Query from a Form


Double check the SQL statement (View SQL)

PARAMETERS [forms]!inputFormCity![lstCity] Text ( 255 );
SELECT People.PeopleNo, People.LastName, People.FirstName, People.City, People.Telephone, People.Score, People.Address
FROM People
WHERE (((People.City)= [forms]![inputFormCity]![lstCity]))
ORDER BY People.LastName, People.FirstName;

Diminishing Returns Example

This is an exercise for Access Level II

Diminishing Returns Form


Event Handlers

Option Compare Database
' The following event handlers are for a form called Diminishing Returns
' This has several Combo Boxes.
' As you make selection in a combo box it populates the data in the next one.
' The choices are cumulative so that in order to see values in a combo box
' the entries must meet all the critera set in the boxes above it.

Private Sub ComboCountry_Change()
' when the user changes the Country set the values for the  ProvState
ComboProvState.Value = Null
ComboProvState.RowSource = "SELECT DISTINCT AllInfo.ProvState" & _
" FROM AllInfo" & _
" WHERE (((AllInfo.Country) = [Forms]![Diminishing Returns]![ComboCountry]))" & _
" ORDER BY AllInfo.ProvState;"
End Sub
Private Sub ComboProvState_Change()
' when the user changes the ProvState set the values for the  City
ComboCity.Value = Null
ComboCity.RowSource = "SELECT DISTINCT AllInfo.City" & _
"  FROM AllInfo" & _
"  WHERE (((AllInfo.ProvState) = [Forms]![Diminishing Returns]![ComboProvState]))" & _
"  ORDER BY AllInfo.[City];"
End Sub
Private Sub ComboCity_Change()
' when the user changes the City set the values for the  LastName
ComboLastName.Value = Null
ComboLastName.RowSource = "SELECT DISTINCT AllInfo.LastName" & _
"  FROM AllInfo" & _
"  WHERE (((AllInfo.Country) = [Forms]![Diminishing Returns]![ComboCountry]))" & _
"   AND(((AllInfo.ProvState) = [Forms]![Diminishing Returns]![ComboProvState]))" & _
"   AND (((AllInfo.City) = [Forms]![Diminishing Returns]![ComboCity]))" & _
"  ORDER BY AllInfo.[LastName];"
End Sub
Private Sub ComboLastName_Change()
' when the user changes the LastName set the values for the  FirstName
ComboFirstName.Value = Null
ComboFirstName.RowSource = "SELECT DISTINCT AllInfo.FirstName" & _
"  FROM AllInfo" & _
"  WHERE (((AllInfo.Country) = [Forms]![Diminishing Returns]![ComboCountry]))" & _
"   AND(((AllInfo.ProvState) = [Forms]![Diminishing Returns]![ComboProvState]))" & _
"   AND (((AllInfo.City) = [Forms]![Diminishing Returns]![ComboCity]))" & _
"   AND (((AllInfo.LastName) = [Forms]![Diminishing Returns]![ComboLastName]))" & _
"  ORDER BY AllInfo.[FirstName];"
End Sub
Private Sub ComboFirstName_Change()
' when the user changes the FirstName the values for the  PeopleNo
ComboPeopleNo.Value = Null
ComboPeopleNo.RowSource = "SELECT PeopleNo " & _
" FROM AllInfo " & _
"  WHERE (((AllInfo.Country) = [Forms]![Diminishing Returns]![ComboCountry]))" & _
"   AND(((AllInfo.ProvState) = [Forms]![Diminishing Returns]![ComboProvState]))" & _
"   AND (((AllInfo.City) = [Forms]![Diminishing Returns]![ComboCity]))" & _
"   AND (((AllInfo.LastName) = [Forms]![Diminishing Returns]![ComboLastName]))" & _
"   AND (((AllInfo.FirstName) = [Forms]![Diminishing Returns]![ComboFirstName])) ;"
End Sub

PDF Export

Print to PDF

In Office 2010 Microsoft introduced the ability to export to PDF files.

Macro to Print a Report to PDF

Access 2010

Image:Access Macro Report PDF 2010.png

Access 2013



Referential Integrity - Orphan Records


Table Index

Multi part indexes

Image:Access 2010 Multi Part Indexes.png

Queries with Calculations

Start of by importing this data from Excel into a table.


we will finish with an Access Pivot Table Form that looks like this.


Creating Pivot Tables - Microsoft

Import from Excel Workbook

  1. Choose External Data | Excel | Browse for the Excel file FruitSales.xlsx

  2. Pick the Sheet Fruit Sales Days

  3. The first row contains Column Headings

  4. Review the Columns (no changes should be required)

  5. Let Access add a Primary Key

  6. Name the Table Fruit Sales Days

  7. You do not need to save the import steps

  8. Here is your new table

  9. Change the Primary Key to FruitSalesDaysID


Query with Calculation based on fields

  1. Create a new query in Design View based on the Table Fruit Sales Days

  2. Include all the fields and then right click in the next empty column and click Zoom
    Type: Amount: [Price]*[Quantity]

  3. View your new Query with the calculated column Amount


PivotTable based on Query

  1. Choose Create | Forms - More Forms | PivotTable

  2. Call the new form FruitSalesDaysAmountPivot

  3. Drag fields as shown here.

  4. Right Click in the Data area and click Hide Details


Finished Result


Access 2013 - No PivotTable or PivotChart

Exercise: Create PivotTable in Excel using Access Database Query as the datasource.

Backup Your Database

Access 2010-Backup yoru Database

Pivot Table Forms

Cheques and Expenses

Start with a Query that looks like this:



Build a Pivot Table Form that looks like this:



View the Query in DataSheet View and Create | Forms | Other Forms | PivotTable


Now build a PivotTable Form that looks like this by dragging the fields to the Row Fields area (LastName and FirstName) and the Column Fields area (Year) and Detail Fields (Amount):


I moved the bottom scroll bar to the right so you could see more data.


And if you had formatted the original query the amount as Currency, then it could look like this:


Once you have the basic PivotTable form - explore what you can do in Design


Medical Rotations / Scheduling

optional discussion

Scheduling Medical Rotations

Example for Andrew Fong, Denise Torresan. VGH

Residents with clinical specialties assigned to different locations on specific dates. Terminology may is be accurate. Prepared from memory after a very brief discussion. These are PivotTable forms.



Download the sample file

Pivot your Data in Access 2010 — Microsoft video

Create PivotTable or PivotChart views in a desktop database (Access 2007/2010)

Note: PivotTable Query Views and Charts are discontinued in Access 2013 Changes in Office 2013

PivotCharts and PivotTables Access PivotCharts and PivotTables are removed There are no options to create PivotCharts and PivotTables in Access 2013. Office Web components are no longer supported. Better charting capabilities in Excel. Enhanced PivotChart and PivotTable capabilities in Excel.


Dates: Expressions for Date Units

In the next few exercises you will use the Expression Builder in the Query Design window. You will also get to know about date related expressions, the Format function, how to sort by Month and Day of the month, and to calculate a person's age

  1. Sorting by Birthdates

  2. Challenges

  3. Design view of the completed query

  4. Building the Query - Expression for Month using Build

  5. MS Access Help (excerpt)

  6. Building the Query - Expressions for Sorting

  7. Building the Query - Expression for Age in Years

  8. Planning Ahead - Birthdays in next two months

  9. Parameter Query - Looking for a specific month

  10. Parameter Query - with a Default value

  11. Display by Quarters

    Date Filters


    Coaching on a Specific Date


    Survey Examples

    Access Survey Example

    Navigation Forms

    These replace Switchboards in the earlier versions of Access.



    Collecting External Data through Email

    This an alternative to using Access 2003 Data Access Pages. See: What happened to Data Access Pages


    Report Snapshots

    Snapshot viewer


    Diminishing Returns Example

    Diminishing Returns Example

    Followup on Topics Covered

    Resources related to exercises


    Other Software (upgrading)

Personal tools