PO Box 634, Rivonia 2128

Tel/Fax: 012-659-0798

Cell: 083 398 7918

E-mail: pj@mevaprod.co.za
 

Duration: 2 days

Location: On-site or Corlett Drive, Bramley

On-site courses available nation-wide

 
 
           

 

                                       

 

 

 

 

 

 

 

Book I

 

Microsoft Access

 

 Fundamentals

 

 

 

 

 

 

 Versions 2000, XP and 2003

           

 

 

 

 

Table of Contents

 

¨       Manual Conventions        

¨       Access Communicates   

¨       Evolution  

¨       Access – Benefits and Limitations          

¨       The Future           

¨       Terminology         

¨       Terms to Remember       

¨       Useful Shortcuts

¨       Access File Types

 

A Relational Database - defined    

 

v      Access Tables    

¨       Section objectives

¨       Exercise T1 – Laying the Foundation

¨       Exercise T2 – Sub-Datasheets

¨       Table Normalisation

¨       Enforcing a 1 ΰ 1 relationship    

¨       Exercise T3 – Set-up a 1 ΰ 1 Relationship        

¨       Resolving ∞ ΰ ∞ relationships   

¨       Exercise T4 – Exploring Many ΰ Many

¨       Table Types         

¨       Tables Summary 

Review Questions – Tables

 

v      Access Queries

¨       Section Objectives           

¨       Select Queries     

¨       The QBE Grid      

¨       Exercise Q1 – Single table queries, Setting parameters

¨       From text to numbers      

¨       Multi-table Queries

¨       Exercise Q2 – Manipulating related data in queries

¨       Inner and Outer Joins

¨       Exercise Q3 – Understanding table joins

¨       Action Queries     

¨       Append (Insert) Queries

¨       Exercise Q4 – Moving data beween tables

¨       Delete Queries

¨       Exercise Q5  – Clean-up the database

¨       Update Queries

¨       Exercise Q6 – Change data to reflect new conditions    

¨       Make Table Queries

¨       Exercise Q7 – Add tables On-The-Fly

¨       Specialised Queries

¨       Crosstab Query

¨       Exercise Q8 – Viewing data in summarised form

¨       Exercise Q9 – Viewing data by Month, Quarter or Year

¨       Introducing DateSerial()

¨       Exercise Q10 – Controlling Dates

¨       Union Queries – An Introduction to SQL

¨       Exercise Q11 – Bring similar data together

Review Questions – Queries

 

v      Access Forms     

¨       Section Objectives

¨       Principles of form design

¨       Exercise F1 – Letting Access do the work

¨       Producing an automatic form      

¨       The Form Wizard

¨       Exercise F2 – Manipulating Contols in forms

¨       Exercise F3 – Forms and their properties

¨       Form Properties discussed

¨       Exercise F4 - To Bind or not to Bind

¨       Adding unbound controls

¨       Exercise F5 – Reduce user input

¨       Option Box Control

¨       Exercise F6 – Another ActiveX Control

¨       Retrieving data from other tables

¨       Exercise F7 – Using Built-In Functions

¨       Exercise F8 – Putting the form on the straight and narrow

¨       Fiixing the Tab Order

¨       A Question of Style

¨       Exercise F9 – Benefit from your creativity

¨       Conditional Formatting     

¨       Exercise F10 – Always know where you are

¨       Creating Sub-Forms

¨       Exercise F11 – 1 ΰ ∞ Form design

¨       Exercise F12 – Prepare to Tab around

¨       Create a custom toolbar   .

¨       Exercise F13 – Offer users exactly what’s needed

¨       Form Filtering Techniques

¨       Exercise F14 – Built-in Search, no work involved

¨       Navigation with unbound forms    .

¨       Setting up Menus

¨       Exercise F15 – Point everyone in the right direction

¨       Enter the DoCmd  Object assisted by the RunCommand Action

¨       Exercise F16 – Useful one-liners

¨       Continuous forms

¨       Exercise F17 – View loads of stuff on one screen

¨       Pivot Tables and Pivot Charts ΰ XP

¨       Exercise F18 – At last we have a charting tool

Review Questions – Forms

 

v      Access Reports  .

¨       Section Objectives           

¨       Exercise R1 – The reason we use databases, for input

¨       Report Properties

¨       Adding Sub-Reports        

¨       Exercise R2 – 1 ΰ 1 Reporting

¨       Report Options

¨       Exercise R3 – Export to other file formats

¨       Grouping information in reports

¨       Exercise R4 – Sensible Grouping

¨       Adding calculated controls to reports.

¨       Exercise R5 – Get the numbers right

¨       Adding a chart to a report

¨       Exercise R6 – From summary data to a chart

¨       Exercise R7 – Use the charting power of Excel

¨       A useful report wizard

Review Questions – Reports

 

v      Access Pages     

¨       Access to the Web

¨       Section Objectives           

¨       Exercise P1 – Going out to the world      

¨       Exercise P2 – The ∞ side of a 1 relationship.

¨       Exercise P3 – We’re not rock stars but groups are great           

Review Questions -  Pages

 

v      Access Macros    .

¨       Section Objectives           

¨       Exercise M1 – Make a Splash

¨       Exercise M2 – Automate the database open routine       

¨       Exercise M3 – Alternative solution           

¨       Exercise M4 – Don’t let mistakes happen

¨       Using macros to control and automate forms

¨       Exercise M5 – Debugging a Macro          

¨       Exercise M6 – Combine Macros with code

Review Questions - Macros

 

v      Additional Topics

v      Linked Table Manager  

v      Link to Excel

v      Startup Parameters

v      Make an MDE file

v      Compacting

v      Encryption/Encoding

v      Security

v      Further Reading

 

Scope

This manual discusses all the primary objects in Microsoft Access except Modules. On completion of the course, delegates will be able to write databases of a high standard and know the rules of maintaining data integrity. A basic database will be completed, a useful template for future work.

 

The course is designed to comply with and exceed the Microsoft Access certification program and with full understanding of this manual, passing the examination should present few problems.

 

The approach to training Access is quite different to that used in Word or Excel. The latter programs are intuitive and within one day, a student can type a presentable document or set-up a simple spreadsheet. Access is more conceptual, the student needs to appreciate a given set of rules that apply globally to the subject matter.

 

In database design, we are concerned with the primary objects, tables, queries; forms etc. that together provide a productive solution to very specific information needs. The fundamentals of table normalisation, data types, relationships and the individual properties of fields or controls need full comprehension if a database is to perform as planned.

 

The manual assumes that delegates have a basic understanding of the Windows operating environment and a sound knowledge of a spreadsheet program or a data management system.