Video 1
Introduction
Meet your instructor, Heather Ackmann, as she introduces you to the features of this course and well as the course design.
- Instructor's Introduction
- Course Introduction
- Exam Objectives
Video 2
Overview Video
If you're reluctant to use Access because you've heard that Access is not a very good program, then this is the Video for you as I attempt to explain how Access has acquired a "bad reputation" among some IT professionals and explain how learning the basics of the program will save you time in the future.
- Introduction to Relational Databases
- Relational vs. flat-file
- Most Important thing to remember: Access is NOT Excel!
- The General Order for Building Databases
- The Scenario for the course
- Building a database for Picaroon Publishing Unlimited, a small literary press, to keep track of employees, customers, orders, authors, and more.
Video 3
The Microsoft Access 2007 Interface
This is a Video for newbies and experienced users alike as I take students on a tour of the new Access 2007 interface. Just like moving to a new town, taking the time initially to learn where the grocery store or the hospital is will save you valuable time "driving around" in the future.
- Getting Acquainted with the Getting Started Screen
- Using Microsoft Access Database Templates
- Creating a New Blank Database in Access
- Opening an Existing Access Database
- Enabling "Certain Content" Using the Message Bar
- The New Interface
- The Microsoft Office Button
- The New 2007 MS Ribbon
- The Quick Access Toolbar
- Dialog Box Launchers
- The Navigation Pane
Video 4
Introduction to Microsoft Access Objects
Designed for complete beginners to building a database, I walk the student through each of the basic MS objects that make-up a database. Becoming familiar with these objects is essential to the overall design process. This Video is the "birds-eye" view of everything we will be designing and building in Access. So, if you are like me and must know where you are going before you go there, you're going to appreciate this Video.
- Types of Basic ObjectsTables
- Queries
- Forms
- Reports
- Getting Familiar with Basic Views in Access
- Datasheet View
- Design View
- Form View/Report View
- Pivot Table View
- Pivot Chart View
- SQL View
- Layout View
- Print View
- Relationships View
Video 5
Designing a Database
An essential Video for anyone new to constructing databases, I walk students through the whole design process from conducting a "Needs Analysis" and gathering data to building a table structure and deciding how far you will need to normalize your data.
- How NOT to Design a Database
- How to Design a Database
- Normalization (Basic Overview)
- Our Scenario
- Our Plan
Video 6
Building Tables
This is an essential Video for anyone who hasn't yet built a table. Using the plan we created in the "Designing a Database" Video, I walk students through table basics and demonstrate all the different ways you can create a table in Access 2007.
- Scenario: Build Tables to store company data for Picaroon Publishing Unlimited
- Building a Table from Datasheet View
- An Brief Introduction to Naming Conventions
- Overview of different Data Types
- How to Add Records
- How to Navigate in a Datasheet using both the mouse and Keyboard Shortcuts
- How to Build a Table in Design View
- Overview of different Data Types
- How to add Descriptions
- Using Advanced Properties
- How to Navigate in Table Design View (using both the mouse and Keyboard Shortcuts)
- Using the Microsoft Table Templates
- Advantages and Disadvantages Templates
Video 7
Modifying Tables
There are a lot more to tables than just building them. Here I show students the more advanced features of constructing tables and controlling data entry.
- How to Control Data Entry
- Creating Input Masks
- Using Validation to restrict data entry
- Creating Lookup Fields
- Using the Access Find and Replace Dialog Box
- Sorting Data in Access
- Filtering Data in Access
Video 8
Extra Practice Building Tables
If you are like the vast majority of the population and need to be shown how to do something more than once, then this is the Video for you. Since tables can be a confusing topic for beginners, I walk students through the creation of ALL the tables we will be using throughout this course. No magic here, just building from the ground up.
Video 9
Creating Basic Relationships
Once tables are built, they are going to need a way to "talk" to each other. Here, students will learn about the various types of relationships as well as the various ways of connecting data inside Access.
- Creating a One-To-One Relationship
- Creating a One-To-Many Relationship
- Enforcing Referential Integrity
- Turning on the Cascade Update Feature
- Turning on the Cascade Delete Feature
- Creating a Subdatasheet
- Entering Information
- Viewing information
Video 10
Advanced Relationships
Here I discuss some of the data limitations that can occur when builders rely on only the basic relationship types (one-to-one and the one-to-many). Then we go through how to build two slightly more advanced relationships in Access, the many-to-many and pass-through relationships.
- Building a many-to-many relationship
- Creating Multiple Keys
- Building a Pass-through relationship
Video 11
Query Basics
Once you put data into Access, you'll occasionally want to visit that data—you'll need a query for that. Here, I'll show you the basics of building a select query.
- The purpose of queries
- Types of queries
- Creating a Select Query
- Using the Query Wizard in Design View
Video 12
Finding Specific Data Using Queries
As your database grows, you'll find it rare to want to see ALL of your data all at once. To limit your query results, you'll need to use a little something called "criteria." Here I'll show you everything you could possible want to know about criteria and operators.
- Introduction to Criteria
- An Introduction to using Operators
- Mathematical
- Relational/Comparison
- Logical
- String
- Other
- Using Wildcards
Video 13
Creating Flexible Queries
Flexibility is a quality I admire about people and MS Access. With parameter queries, you can design one query that will output or retrieve multiple data results based around user input. With parameter queries, you can save your end-users time and give them the choice of what data they want to see when.
- Benefits of a using flexible query
- Creating a flexible query
- Using Wildcards with a flexible query
Video 14
Creating Calculations inside a Query
Creating calculations is essential for many aspects of data analysis, manipulation, etc.; however, you can also use built-in functions to override table formatting options, data normalization, and output. Here I introduce students to creating their own custom calculations and show them how to use popular built-in functions.
- Introduction to Calculations
- Creating a Calculated Detail Field
- Calculating a Group of Records
- Introduction to Built-in Functions
- The DateDiff Function
- Format Functions
Video 15
Introduction to Joins
Most times users to Access use queries to see data that exists in their database. Sometimes, though, users want to see data that doesn't exist in their database. You'll need to know how to change the join type for that.
- Introduction to Query Joins
- Creating an Inner Join
- Creating an Outer Join
- Creating a Self Join
Video 16
Introduction to Action Queries
Rather than just having your query show you something, you might want to have your query do something, like build a new table or add records to an already existing table. In this Video, I show students how to make queries work for them by demonstrating how to build various action queries.
- Creating an Append Query
- Creating a Delete Query
- Creating a Make-Table Query
- Creating an Update Query
Video 17
Using Special Query Wizards
In addition to building queries yourself, Access provides you with several wizards that are designed to perform very specific tasks. In this Video, I'll show students how to use some special query wizards, such as the crosstab query wizard.
- The Crosstab Query Wizard
- The Find Unmatched Query Wizard
- The Find Duplicates Query Wizard
Video 18
Introduction to PivotTables and PivotCharts
If you are one of those crazy number crunchers, you'll need to know how to use a PivotTable and PivotChart. In this Video, I'll introduce you to the basics of creating and modifying these elusive pivoting structures.
- Creating a PivotTable
- Modifying a PivotTable
- Formatting a PivotTable
- Creating a PivotChart
- Modifying a PivotChart
- Formatting a PivotChart
Video 19
Creating Basic Forms
If your database will have multiple hands inside your Access cookie jar, so to speak, you'll need to find a way to make your database more accessible to end-users. Forms are the way to go! In this Video, I show students the basics of creating user-friendly forms.
- An overview of Form Views
- Creating Forms
- Using the wizard
- In design view
- Modifying Forms
- In layout view
- In design view
- Adding Special Tools
- Adding Images to forms
- Creating an Option Group Control
- Creating a Calendar Control
- Creating Command Buttons
Video 20
Designing Macros
Rather than performing a lot of simple actions over and over, why not have Access just perform the series of actions with the click of one button? By learning to write macros, students can find ways to automate a lot of commonly repeated tasks.
- Creating a Macro
- Adding a macro to form control
- Embedded Macros
- Controlling Data through Macro Conditions
- Building a macro to run automatically
Video 21
Creating Basic Reports
You won't want you data to simply exist on a computer. At some point, you'll need to print the data in order to hand it over to someone else. In this Video, I'll show you several methods for creating reports that will impress.
- Creating Reports
- Formatting Reports
- Adding Calculated Fields
- Sorting and Grouping Data
- Exploring Printing Options
- Adding a Chart to a Report
Video 22
The Switchboard Manager
When your database is close to being complete, you'll need to build a switchboard to help end-users locate the reports they use the most. That way, end-users won't go exploring and break something they shouldn't be able to see in the first place.
- Creating a Switchboard
- Editing a Switchboard