Access Intermediate

Who is this for?
You’ll probably be using Access regularly and can happily create a table and enter data, create a query to select data, and create a form or report with the Wizard to input or print out data.

What does it cover?
Building on the Access Essentials, this course nudges the user to creating more complex databases that can be used by others and operated in a shared environment.  The course considers performance issues such as indexing tables for fast querying and reporting, controlling data entry with more advanced validation, securing and ensuring data integrity with relationships and database management such as backing up and password protecting.  Querying is revisited with emphasis on summarising data, using crosstabs for analysis, and calculated fields to create new data.  More advanced form and reporting techniques are considered to make the database look and behave in a professional manner.

How long is it?
One day as a group, or can be split into several sessions if required for individuals.

Where is it run?
On your site, at home, or via one of the venues we use.

Access Review
Databases & access terminology reviewed
Access Objects: Tables, Queries, Forms, Reports
The Navigation pane & Access views
Creating Tables
Revision of fieldnames, data types & notes
Revision of validation checks
Upper/lower limit checks
Using dates in validation
Lookup revision
Input masks
Purpose & limitations
The characters used
Testing the input mask
Record level validation
Comparing fields together
Creating calculated fields
Understanding Primary keys & Relationships
Single primary keys
Composite primary keys
Relating records using foreign keys
Creating relationships between tables
Enforcing Referential Integrity & why
Cascading deletes & updates
Printing relationships
Why bother to index a table
Deciding which fields to index
Creating a single field index
Creating a multiple field index
Summarising data with queries
Queries review
The 3 stages of creating a query
Multi table queries
Using IIF() in a query
Creating calculated fields
Reusing queries – adding Parameters & datatypes
Creating a crosstab query
The 3 types of query joins & when to use them
Creating summary queries
Summing & counting data
Using a query as input to a summary query

Action Queries
The pros & cons & uses
Updating & deleting data
Creating new tables and appending data
Using action queries for archiving
More Form techniques
Creating forms reviewed
Understanding Sub forms
Creating/modifying a sub form
Adding a sub form to a main form
Link the sub form to the main form
Linking forms to queries
Using the tab control / sub forms on a tab control
Setting the tab order
Adding tooltips
More Reporting techniques
Adding calculations
Adding data summaries & totals
Showing totals only
Creating mailing labels
Linking a report to a parameter query
Linking a report to a form
Adding page breaks
Review of grouping data
Breaking groups onto new pages
Formatting groups
Database management & options
Backing up a database
Compact & repair
Password protection
Hiding the navigation pane
Creating object groups
The database documenter
Splitting a database
Why do this?
Creating the backend
Creating the front end
Making the link

Download the Course Outline here