ACCESS ADVANCED

Course Overview

The Access Advanced course builds on your current knowledge gained from the Access Essentials course or equivalent. This 2 day course focuses on the more advanced features of Microsoft Access such as database maintenance, importing and exporting data using other compatible applications, building and executing more advanced queries such as action and crosstab queries, changing join types to achieve a desired reporting outcome, implementing security and automating tasks with macros and VBA. Students will also gain a much deeper understanding of table relationships using cascade update and delete options as well as being able to work confidently with properties and expressions in queries forms and reports. The knowledge attained from this course will allow students to provide the additional polish to your existing databases or upcoming projects.

Who Should Attend

Students should be confident in creating and designing tables, queries, forms, reports and have a basic understanding of table relationships before attending this course. This course will provide students with a much deeper knowledge and appreciation of creating, using and developing relational databases and associated tools, features and techniques.

Course Prerequisites

Microsoft Access Essentials or equivalent

Raising the Bar

1300 937 782

www.rtb.com.au

Database Maintenance

Back Up Database
Analyze Performance
Compact and Repair
Database Documenter
Object Dependencies
ACCDE files Split the Database

Import and Export Data

Export to Excel
Export to Word
Export to PDF
Use a Saved Export Task
Import from Excel to a New Table
Append Data from Excel to an Existing Table
Normalise Imported Tables
Link to Excel
Import or Link from Another Access Database
Access 2013 Doesn’t Support Data Collection

Advanced Queries

About the Expression Builder
Create a Calculated Field
Show Top Values
Create Function Queries
Concatenations and Null Precedence
Using Null as a Criterion
Use Calculations to Impose Case
IIf Calculations
Nested IIf Functions
Calculating with Dates
Format Fields Querying Multivalue Fields

Action and SQL Queries

Enabling and Running Action Queries
Create an Update Query
Create an Append Query
Create a Delete Query
Automatic Archiving
Create a Make Table Query
Hide Action Queries
SQL
Outer Joins
Create a Union Query Other SQL-specific queries

Subforms and Calculations

Automatically Create Forms and Subtables
SubDataSheets in SubTables
Manually Insert a Subform
Default View for the Subform
Calculate Totals for Subforms
Hide a Subform
Hide Columns in a Combo Box
Linking to Combo Box Columns
Preventing Data Entry in Calculated Fields
Cascading Combo Boxes
Create a Second Combo Box
Add a Selection Criterion to the Client Box
Use the Refresh Macro

Charts and PivotTables

Create a Chart
Modify the Chart
Remove the Dummy Data
Conditional Formatting
PivotTables And PivotCharts Are Not Supported In Access 2103

Macros

What are Macros?
Create a Simple Macro
Collapse and Expand Action Arguments
Run and Edit Macros
Adding Comments
Access Macro Actions
Assign a Macro to an Event
Create a Group of SubMacros
Create Buttons on a Form
Copy and Paste a Control
Create a Conditional Macro
Error Trapping
Interactions - MessageBox
Interactions - MsgBox as a Function Interactions - InputBox and SetTempVar

Security

Where is User-Level Security?
Convert the Database
Enable Content
Encrypt with a Database Password
Add Trusted Locations
Create a Self-Signed Certificate
Package and Sign the Database
Extract the Packaged Database Remove a Trusted Publisher

Modules

Create a Module
Convert a Macro to VBA
Interpret the VBA Code
Edit the Code
IntelliSense
Standard and Class Modules
Add VBA to a Form Event
VBA Errors