Using Microsoft Access 2003
Day 1: Queries and Tables
| Duration: | 1 day |
| Prerequisite: | Basic knowledge of the windows environment, basic keyboard skills. Familiarity with the Access environment is helpful but not required. |
| Course Materials: | Training book (350 pages) and handouts. |
| Prices: | $195 for 1 student (one-on-one training) $160 each for 2 to 3 students $130 each for 4 to 6 students $130 for the first 6 students, $100 for each additional student |
| Description: | This class is Day 1 of a 3-day course that teaches you how to store, manage, manipulate, and analyze data using Microsoft Access. For the most part, the course concentrates on queries, forms, and reports. In other words, this course teaches you what you need to know about how to effectively use Microsoft Access. The first day teaches you how to manipulate important data by building powerful queries. After learning how to create basic queries, we move on to understanding criteria expressions, multiple-table queries, totals, parameter, and action queries. You have the option of taking all 3 days of training (highly recommended), or cherry-picking topics for a 1 or 2 day custom course. If you just want an Introduction to Microsoft Access, we can put together that class as well. |
Understanding Relational Databases
- Planning and Creating a Simple Database
- Purpose of the Database
- Determining the Fields You Need
- Creating Tables
- Determining Relationships
- Referential Integrity
Creating a Basic Query
- Sorting Records
- Sorting on a Single Field
- Sorting on Multiple Fields
- Filtering Table Data
- Filtering by Selection
- Filter Excluding Selection
- Filtering By Form
- Learning About Filter Criteria
- Creating a Filter
- Creating a Query
- Creating a New Query Object
- Selecting the Fields to Include in the Query
- Entering the Query Criteria
- Excluding a Field from the Query Results
- Selecting Only the Top N Values
- Setting Field Properties
- Running the Query
- Guidelines for Creating a Query For Business Users
- Querying for a Mail Merge
- Working With A Query Dynaset
- Understanding the Datasheet View
- Navigating Fields
- Entering Data
- Adding More Records
- Navigating Records
- Selecting a Record
- Copying a Record
- Deleting a Record
- Formatting the Datasheet
- Working with Query Properties
Building Criteria Expressions
- Using Operands in Criteria Expressions
- Literals
- Identifiers
- Functions
- Using Operators in Criteria Expressions
- Comparison Operators
- Arithmetic Operators
- The Like Operator
- The Between...And Operator
- The In Operator
- The Is Null Operator
- Compound Criteria and the Logical Operators
- Entering And Criteria
- Entering Or Criteria
- Using the Logical Operators
- Understanding Operator Precedence
- Setting Up a Calculated Column
- Calculating Inventory Value
- Calculating Discounted Invoice Totals
- Using The Built-in Functions
- Using Text Functions
- Using Date and Time Functions
- Using Math Functions
- Using Financial Functions
- Working with the Expression Builder
Working with Multiple-Table Queries
- Relational Database Fundamentals
- How a Relational Design Reduces Repetition
- Separating Your Data into Tables
- Adding Foreign Keys to the Tables
- Establishing Links Between Related Tables
- Types of Relational Models
- The One-To-Many Model
- The One-To-One Model
- The Many-To-Many Model
- Enforcing Referential Integrity
- Establishing Table Relationships
- Understanding Join Lines
- Types of Joins
- Adding Tables to the Relationships Window
- Joining Tables
- Editing a Relationship
- Removing a Join
- Working with Multiple Tables in a Query
- Adding Fields From Multiple Tables
- Nesting Queries Within Queries
- Joining Tables within the Query Design Window
- Creating Other Types of Joins
- Creating Outer Joins
- Creating Self-Joins
- Creating Theta Joins
- Creating a Unique Values Query
- Drilling Down to the Order Details
Creating Advanced Queries
- Creating a Totals Query
- Displaying the Total Row in the Design Grid
- Setting Up a Totals Query On a Single Field
- Setting Up a Totals Query On Multiple Fields
- Filtering the Records Before Calculating Totals
- Creating a Totals Query for Groups of Records
- Grouping on Multiple Fields
- Creating a Totals Query Using a Calculated Field
- Creating a Totals Query Using Aggregate Functions
- Combining Aggregate Functions and Totals
- Creating Queries That Make Decisions
- Making Decisions with the IIf Function
- Making Decisions with the Switch Function
- Calculating a Customer Discount Rate
- Calculating a Simple Discount Rate
- Calculating a Complex Discount Rate
- Running Parameter Queries
- Creating a Simple Query Parameter
- Specifying the Parameter Data Type
- Running Action Queries
- Modifying Table Data with an Update Query
- Removing Records from a Table with a Delete Query
- Creating New Tables with Make-Table Queries
- Adding Records to a Table with an Append Query
Creating PivotTable Queries
- What is a PivotTable?
- How PivotTables Work
- Some PivotTable Terms
- Creating a One-Dimensional PivotTable
- Display Data Field Details
- Displaying the Sum of the Data Field Values
- Hiding and Showing the Data Details
- Inserting an AutoCalc Data Field Summary Calculation
- Changing the AutoCalc Calculation Type
- Creating a Calculated Field
- Removing a PivotTable Field
- Creating a Multiple-Field One-Dimensional PivotTable
- Creating a Two-Dimensional PivotTable
- Analyzing Customer Orders by Product Category
- Adding a Temporal Dimension to the PivotTable
- Working with the Built-In PivotTable Date Features
- Adding Custom Date Fields
- Viewing Product Orders by Day of the Week
- Viewing Employee Orders by Custom Quarter
- Filtering a PivotTable
- Using the PivotTable AutoFilters
- Displaying Only the Top Or Bottom Items
- Grouping Field Items
- Adding a Filter Field
- Pivoting a PivotTable
- Moving a Field to a Different Area
- Changing the Field Order
- Formatting a PivotTable