IG047F13A

From CourseWiki

(Redirected from IG047)
Jump to: navigation, search



Microsoft Access Level II - Troubleshooting & Designing Databases

Overview

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.

Objectives

Theoretical

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

Practical

  • 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

Prerequisites

Academic

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

Practical

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.

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.

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


Contents

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

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

Image:Access 2013 Home Tab.png

Image:Access 2013 Back Stage View.png


Subdatasheets

Create Subdatasheet

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

Image:SubdatasheetsCreate.png

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.

Image:SubdatasheetsRemove.png


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).

Image:AccessSplitForm01.png

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

Image:AccessSplitForm02.png


Reference: Create a Split Form

Object Dependencies

Image:Access2010ObjectDependencies.png

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.

Notes

  • 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

Image:Access2010SecurityBar.jpg

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

Image:Access02Relationships01.png

Query Design

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

Expressions

Image:Expression.gif

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

WeekdayName(Weekday([Birthdate]))

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

Query Column Expressions

Age and Dates

Age


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"

Simple


[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",
([Age]*[Height]/[Weight]*1.3),
([Age]*[Height]/[Weight]*1.1)
)

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


[forms]![inputFormCity]![lstCity]

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

Image:DiminishingReturnsForm.png

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



Validation

Image:Access2010TableValidation.png

Referential Integrity - Orphan Records

Image:PeopleWithoutPayments.png


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.

Image:FruitSalesPivotExcelToImport.png

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

Image:FruitSalesPivot01.png

Creating Pivot Tables - Microsoft

Import from Excel Workbook

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

    Image:FruitSalesPivotExcelImport01.png
  2. Pick the Sheet Fruit Sales Days

    Image:FruitSalesPivotExcelImport02.png
  3. The first row contains Column Headings

    Image:FruitSalesPivotExcelImport03.png
  4. Review the Columns (no changes should be required)

    Image:FruitSalesPivotExcelImport04.png
  5. Let Access add a Primary Key

    Image:FruitSalesPivotExcelImport05.png
  6. Name the Table Fruit Sales Days

    Image:FruitSalesPivotExcelImport06.png
  7. You do not need to save the import steps

    Image:FruitSalesPivotExcelImport07.png
  8. Here is your new table

    Image:FruitSalesPivotExcelImport08.png
  9. Change the Primary Key to FruitSalesDaysID

    Image:FruitSalesPivotExcelImport09.png

Query with Calculation based on fields

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

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

    Image:FruitSalesPivotExcelImport11.png
  3. View your new Query with the calculated column Amount

    Image:FruitSalesPivotExcelImport12.png

PivotTable based on Query

  1. Choose Create | Forms - More Forms | PivotTable

    Image:FruitSalesPivotExcelImport13.png
  2. Call the new form FruitSalesDaysAmountPivot

    Image:FruitSalesPivotExcelImport14.png
  3. Drag fields as shown here.

    Image:FruitSalesPivotExcelImport15.png
  4. Right Click in the Data area and click Hide Details

    Image:FruitSalesPivotExcelImport16.png

Finished Result

Image:FruitSalesPivot01.png

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:

Image:AccessQueryPeopleCheques01.png


Image:AccessQueryPeopleCheques02.png


Build a Pivot Table Form that looks like this:

Image:AccessPivotTableForm02.png


Steps:

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

Image:AccessPivotTableForm06.png


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):

Image:AccessPivotTableForm07.png

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

Image:AccessPivotTableForm08.png


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

Image:AccessPivotTableForm03.png

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

Image:AccessPivotTableForm04.png


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.

Image:RotationsSpecialty.jpg

Image:RotationsLocation.jpg


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

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

    Image:DateFilters.png

    Coaching on a Specific Date

    Image:CoachingOnSpecificDate.png

    Survey Examples

    Access Survey Example

    Navigation Forms

    These replace Switchboards in the earlier versions of Access.

    Image:Access2010NavigationForms01.png

    Image:Access2010NavigationForms02.png

    Collecting External Data through Email

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

    Image:ExternalDataEmail01.png

    Report Snapshots

    Snapshot viewer

    Resources



    Diminishing Returns Example

    Diminishing Returns Example


    Followup on Topics Covered


    Resources related to exercises

    Resources

    Other Software (upgrading)




Personal tools