- Lesson 1 - Welcome to Train Signal!
-
Meet your instructor, Heather Ackmann, as she introduces you to the features of this course as well as the course design.
- Instructor’s Introduction
- Course Introduction
- Exam Objectives
- Lesson 2 - Microsoft Access: The Back Story
-
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.
- Lesson 3 - Welcome to Microsoft Access 2007
-
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
- Lesson 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
- Lesson 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
- Lesson 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
- Lesson 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
- Lesson 8 - Building and Modifying Tables: Extra Practice
-
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.
- Lesson 9 - Creating 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
- Lesson 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
- Lesson 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
- Lesson 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
- Lesson 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
- Lesson 14 - Creating Calculations Inside Queries
-
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
- Lesson 15 - Introduction to Query 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
- Lesson 16 - Intro 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
- Lesson 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
- Lesson 18 - Creating 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
- Lesson 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
- Lesson 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
- Lesson 21 - Creating Basic Reports
-
You won’t want your 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
- Lesson 22 - Creating a Switchboard
-
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