Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Access 220
Get notifications when this page is updated

Intro

In lesson 220, you will learn how to create mathematical calculations and use functions in Microsoft Access queries, explore different query join types like inner and outer joins, work with calculated totals and form footers in continuous forms, and review referential integrity concepts including cascade deletes. This lesson builds on concepts from previous courses, and you'll follow along using the same sample database we've used since Access 101.

Transcript

Welcome to Microsoft Access 220 Advanced Queries Part 1, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to learn how to create mathematical calculations in queries. We are going to see how some functions work in queries. We are going to learn about different query join types, including inner joins and outer joins. We are going to see how to create calculated totals and form footers of continuous forms. We will also go over referential integrity, including cascade deletes between our tables.

This class follows Microsoft Access 207, and we will be using the same database that we have been working on since Access 101. I strongly recommend that you watch all the previous classes before starting with this class.

For the example videos, I will be using Microsoft Access XP, the 2002 edition, and Windows XP. However, most of the material in this lesson is valid for every previous version of Access. Some of the things I am going to show you, when we get into the global relationships, will be slightly different depending on whether or not you have an older version of Access. However, all of the core material that I will be teaching you in this lesson is valid for just about every version of Access.

I recommend you sit back, relax, and watch the videos one time through, then follow along. Do the examples yourself, build the database that I am building yourself. Do not try to apply what you learn immediately to whatever programs you have been working on. What I want you to do is sit down and focus on this database, the sample database we are building. Do the examples with me. Do not just cheat and use my database, and you will learn a lot better that way.

Now, if you have not been building your database yourself since Access 101, or if you just prefer working from a fresh, clean copy, that is okay. You can get a copy of my database file at the website www.accesslearningzone.com/access/220, that is the page for this class.

If you purchased this course on CD-ROM or downloaded it from our website in the full self-extracting executable file, you will find that it has been installed in your help folder in your Program Files 599 CD Access 220 folders. If you look at your Program Files folder, whatever that happens to be, and under this particular class you will see a help folder, and that is where the database file will be installed.

But I cannot stress strongly enough that you will get the most out of this course if you actually build this database yourself.

A little lesson here, for many, many years, I did not bother going through the sample database that comes with Microsoft Access, the Northwind Traders database, even though the different books that I read that I taught myself Access from suggested that you go through and play with it. After years of fumbling around trying to figure out what I saw, I finally went in and poked around in that Northwind Traders database, and I learned a lot of stuff just by looking at what they did.

The moral of the story is if you do this database with these examples the way that I am suggesting, you will get more out of it than just trying to watch this and then working on your own database. Do what I am doing, you will see how things work, and you will learn a lot more that way.

Quiz

Q1. What is one of the main topics covered in this lesson of Microsoft Access 220 Advanced Queries Part 1?
A. Creating forms in Access
B. Mathematical calculations in queries
C. Importing data from Excel
D. Printing reports

Q2. Which type of query join is mentioned as being discussed in this lesson?
A. Only cross joins
B. Only right joins
C. Both inner and outer joins
D. Only self joins

Q3. What should students do to get the most out of the course?
A. Only watch the videos without doing the examples
B. Immediately apply the concepts to their own projects
C. Build the sample database themselves while following along
D. Use the instructor's finished database without doing any exercises

Q4. According to the lesson, what is covered in addition to mathematical calculations in queries?
A. How to send emails from Access
B. Creating calculated totals and form footers in continuous forms
C. Printing mailing labels
D. Using macros in Access

Q5. Where can students get a fresh, clean copy of the database file for this class?
A. Only from Microsoft's official website
B. By contacting the instructor directly
C. From www.accesslearningzone.com access 220 page
D. By requesting a CD via mail

Q6. What is said about the compatibility of the material in this course with different Access versions?
A. The material only works with Access XP
B. The material is valid for almost every version of Access
C. The material only applies to Access 2007 and newer
D. The material does not work on Windows XP

Q7. What lesson did the instructor share about his own experience with learning Access?
A. He learned best by using YouTube tutorials
B. He wished he had used the Northwind Traders sample database sooner
C. He only used online forums for learning
D. He never made mistakes while learning Access

Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-B; 7-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on Advanced Queries in Microsoft Access. In this lesson, the topics revolve around creating mathematical calculations inside queries. I will demonstrate how certain functions can be used effectively within queries, and explain the different types of query joins, specifically highlighting both inner and outer joins. Another important area covered is how to set up calculated totals as well as create form footers in continuous forms. Additionally, I will explain concepts related to referential integrity, including how cascade deletes work between tables.

This class builds on material from previous lessons, specifically Access 207, and continues using the same sample database that was introduced in Access 101. If you are just joining in, I highly recommend you review all earlier classes first to ensure that you are up to speed with how the database has developed over time.

My demonstrations today are in Microsoft Access XP (the 2002 edition) running on Windows XP. However, almost everything included in this lesson is relevant to earlier versions of Access, so you will be able to follow along no matter which version you are using. Some aspects, such as global relationships, may appear a little different depending on your Access version, but the essential material applies universally.

I always suggest that you first watch the instructional videos all the way through. Once you have a sense of the material, follow along with me and replicate the examples using the database we are building together. Try to resist the urge to immediately apply these lessons to your own existing projects. Instead, focus on working through my examples with the sample database. I have found that actually building and experimenting with the training database yourself is a far more effective learning approach than simply copying my file or skipping ahead.

If you have not been maintaining your own database since Access 101 or prefer to start fresh, that is perfectly fine. You can download a clean copy of the database file from my website on the Access 220 class page. For those who have purchased this course on CD or downloaded the full version from the website, you will find the database file in the help folder, inside the Program Files directory under 599 CD Access 220.

I cannot emphasize enough that you will get far more out of this course if you actively participate by building the database yourself. Speaking from personal experience, I neglected the Northwind Traders sample database that shipped with Access for many years, despite many books recommending that I explore it. Once I finally did, I realized how much I could learn simply by observing established examples and seeing how they functioned in practice. Following along step by step with these video lessons and working directly in the sample database is the best way to master these concepts.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Creating mathematical calculations in queries
Using functions in queries
Understanding different query join types
Working with inner joins
Working with outer joins
Creating calculated totals in queries
Adding form footers to continuous forms
Setting up referential integrity
Implementing cascade deletes between tables