Microsoft Access Basic Level

The purpose of this seminar is to teach the participants the advantages of using an efficient database system. The seminar will also get into the details of database design, and creation and will explain how a database can be used for efficient management and control.

This seminar prepares for the ECDL and MOS respective certification.

Upon seminar completion the participants will learn: •The theory on databases and database design •Use and manipulate an existing database: Tables and Relationships •Create Queries, Filters and Mail Merge •Build User Friendly applications - Designing and Creating Forms •Build Management Reports and Automation

Unit 1: Computerised databases
Database Management System (DBMS) for defining constructing and manipulating databases
• Relations - reasons for having more than one relations
• Records and Fields
• Relationships
• one-to-many relationship
• many-to-many relationship
• Controlling Redundancy

Unit 2: Relational Databases
• Primary and Foreign Keys - Relationships
• Integrity Constraints in Relational Database Models
• Key Constraints
• Entity Integrity Constraints
• Referential Integrity Constraints
• Informal Design Guidelines for Relation Schema
• Reducing the redundant values in records
• Reducing null values

Unit 3: Introducing the BEST TOY case study
• The customers, toys, sales and suppliers table
• The relationship window
• Entering data using the tables (entering customers and sales records)

Unit 4: Using and manipulating an existing database: Tables and Relationships
Tables
• Design View and Datasheet View
• Records and Fields
• Data types of fields
• Properties of fields
• Field size property
• Format property
• Input mask property
• Validation rule and text
• Required Property
• Index property
• Creating a table with the aid of the wizard
• Creating a table from the design view
• Exercise: Building an employees table
Building relationships between tables to relate information concerning the same records
• relating each employee with the sales he is responsible for
Referential Integrity
• Enforcing Cascade Update and Cascade Delete

Exercise on referential integrity
Project: Building a mailing database

Unit 5: Queries, Filters and Mail Merge
Using databases for everyday jobs
• Sorting, Searching and Replacing Data
• Finding a customer according to specific criteria
• Searching for a specific sale
Using filters
• Filter by selection
• Filter by form
• Advanced filter

Project: Building a mailing database- continued
 

Queries
Select Queries
• Selecting specific information from the database e.g. customers of a specific supplier
• Parametric queries and their advantages

Exercise on select queries
Project: Building a mailing database -continued

Using calculated fields in queries
• Calculating the Total amount from each sale
• Setting the properties of calculated fields

Summarizing data in queries
• Customers with sales turnover greater than a specific value
• Finding out the best selling products of your company
• Rating of employees according to their performance
• Performing calculations - total purchases made by each customer; sales record of an employee

Exercise on calculated fields and summarizing data queries
Project: Building a mailing database


Unit 6: Building User Friendly applications - Designing and Creating Forms
Entering and Validating data through forms -
Customising Forms to meet your needs
• Adding labels
• Changing the colours
• Using command buttons
• Exercise on command buttons
• Using combo boxes
• Combo box that displays data from a table or query
• Combo box that displays a static list
• Combo box to select data on a form
• Using check boxes
• Creating a user friendly customer form and an employee form
• Creating forms based on queries
• The Auto lookup query
• Formatting forms based on queries with the aid of command buttons. Combo boxes and check boxes
• Project: Building a mailing database - continued

Unit 7: Building Management Reports
• Creating Management Reports
• Selecting the information that will appear on the report with the aid of queries
• Formatting the report

This course is for anyone who wishes to build a database for efficient management of large amount of data.

1. Lecturing 2. Discussion 3. Exercises

The participants should have good knowledge of the Windows environment and be able to open and save files in this environment. Basic knowledge of any Microsoft product (i.e.MS Word or MS Excel) is recommended. No previous experience with databases is needed.