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 221
Get notifications when this page is updated

Intro

In lesson 221 you will learn how to create and use aggregate queries in Microsoft Access, including generating sums, averages, and counts in your queries. I will show you practical examples, such as calculating total sales by state, as well as how to add totals to the bottoms of forms and reports. We will also cover building a work log and a timesheet to track employee time. This class builds on the previous lesson, Microsoft Access 220, and is part 2 of our series on advanced queries.

Transcript

Welcome to Microsoft Access 221 Advanced Queries Part 2. Brought to you by accesslearningzone.com. I am your instructor, Richard Rost.

Today's class is going to continue our series on Advanced Queries. Today we are going to learn aggregate queries. Aggregate queries are very powerful. They allow us to create sums, averages, and counts, as well as different totals inside of our queries.

For example, we can take all of our sales and sum them up to see all the sales by state. We are going to see how we can do a couple of different examples of aggregate queries today.

We are also going to work with putting totals on the bottoms of forms and reports. We are going to make a work log and a timesheet so we can track employee time, clocking in and clocking out.

The prerequisite for this class is Microsoft Access 220, which was the first part of the advanced query session. This is also the 13th class in a long series of Microsoft Access courses. I recommend that you start from 101 and work your way up to 220 before taking this one.

We are still using Microsoft Access XP 2002 edition. This is valid for any version of Access. The material that I am going to teach you in today's class is valid for pretty much every version of Access from 97 all the way up to 2003. Any of those versions will be fine.

I have been saying this since the first class: sit back, relax, and watch the videos one time through. This is a long class today, so sit back, relax, and enjoy it. See what I am doing. Get a feel for it. Then I want you to go through a second time, working with me and doing all the examples.

Once you have mastered that, once you have gone through the examples, you can apply what you have learned in today's class to your database. Do not try doing what I am doing in your database right away because you are going to mess things up. I know I do. If you have any examples, then apply it to what you are working on.

The sample database files can be found online on our website, accesslearningzone.com/access/2.21. That is the folder on our website where all these sample files and some course notes can be found. If you install them off of the CD-ROM that we mailed you (if you bought our CD-ROM), the sample database files should also be in the help folder under Program Files, 5.9, CD, Access 221.

The most important thing is to do the examples. Watch the videos, then do my examples. You will get the most out of this course if you actually take the hour and a half of this class, sit down, and run through the material that I present. Even if you do not do everything exactly the way that I do it, you will still benefit a lot from just playing with this material and seeing how it works.

Most important of all, have fun. Access is a good time. I have a lot of fun working with Access. It is my favorite course to record. I strongly suggest that if you want to learn Access, have fun with it. Do something that you enjoy doing. If you like working on a sample hobby, like a baseball card catalog or anything that you do, have fun with Access and enjoy it. Try my examples. That is the best way to learn.

Quiz

Q1. What is the main topic covered in Microsoft Access 221 Advanced Queries Part 2?
A. How to create simple data entry forms
B. How to use aggregate queries for sums, averages, and totals
C. Importing data from external sources
D. Designing user interfaces

Q2. Aggregate queries in Access are primarily used to:
A. Validate data within forms
B. Create sums, averages, counts, and other totals in queries
C. Set up user permissions
D. Design macros for automation

Q3. According to the instructor, what should students do after watching the video tutorial the first time?
A. Immediately apply changes to their own databases
B. Take a written test
C. Watch the videos a second time while working through the examples
D. Skip to the next course

Q4. What sample files are mentioned as being available for students to use?
A. Files posted on YouTube
B. Files available on accesslearningzone.com and on the course CD-ROM
C. Files that come with Microsoft Access
D. Files sent via email

Q5. What is the recommended prerequisite for this class?
A. Microsoft Access 2003 Essentials
B. Microsoft Access 101 only
C. Microsoft Access 220 Advanced Queries Part 1
D. No prerequisites are needed

Q6. The material taught in this class is valid for which versions of Microsoft Access?
A. Only Microsoft Access XP 2002
B. Access 97 through Access 2003
C. Only Microsoft Access 2010 and newer
D. Only Microsoft Access 2016 and 2019

Q7. What should students avoid doing right away, according to the instructor?
A. Reading the course notes
B. Applying what the instructor does directly to their own databases before practicing with the video examples
C. Downloading the help folder
D. Watching the videos at double speed

Q8. What is the best way to get the most out of the course, according to the instructor?
A. Take notes without watching the videos
B. Only read the course slides
C. Watch the videos and do the examples presented
D. Memorize all Access terminology

Q9. What attitude does the instructor encourage when learning Access?
A. Frustration is normal and expected
B. Have fun and enjoy working with Access
C. Rushing to complete the course
D. Focusing only on business databases

Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-B; 7-B; 8-C; 9-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 continues our series on Advanced Queries in Microsoft Access. In this lesson, I am going to cover aggregate queries, which are an essential tool for summarizing data. Aggregate queries let us perform calculations such as sums, averages, and counts directly within our queries. For instance, you can set up a query to total all your sales by state, providing valuable insights into your data.

We will go through a variety of examples to help you understand how aggregate queries work. In addition, I am going to demonstrate how to add totals to the bottom of your forms and reports, which is particularly useful for displaying summary information to your users. We will also create a work log and a timesheet to track employee hours, including clock-in and clock-out details.

Before starting this course, make sure you have completed Microsoft Access 220, which is the first part of the advanced query lessons. This is actually the thirteenth class in a larger series of Access courses, so I recommend starting from Access 101 and progressing through 220 if you have not taken those yet.

Although I am using Microsoft Access XP 2002 in these demonstrations, everything we will cover today applies to versions from Access 97 through 2003, so you should have no trouble following along regardless of your software version.

As always, I encourage you to watch the video the first time without trying to work along with it. Just watch, relax, and get a sense of the material. This class is fairly long, so take your time and see how each example is done. Then, after your first pass through the video, go back and work through the examples with me. This hands-on practice will help you understand and retain the material.

Once you are comfortable with the examples from class, try applying what you have learned to your own database. I recommend not trying to make changes to your database until you have practiced and understood the methods using the sample files, otherwise you might run into problems. When you do move on to your own projects, you will find it easier to adapt the techniques from the lessons.

You can find all the sample database files and course notes online at accesslearningzone.com in the access 2.21 section. If you received the CD-ROM version, the sample files are also available in the Help folder under Program Files.

The most important advice I can give is to actually do the examples. Spend time with the material and experiment with the concepts I show you. Even if you do not follow every step precisely, you will benefit from playing with the queries and seeing how different ideas work.

Above all, remember to have fun. Working with Access should be enjoyable. Experiment with the database using subjects and hobbies that interest you. If you like working on something like a baseball card catalog or any personal interest, use that as your subject to practice these skills. Trying out my examples and customizing them for your own needs is the best way to really learn Access.

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

Aggregate queries (totals, sums, averages, counts)

Creating aggregate queries in Access

Summing sales data by state

Using aggregate queries with forms

Adding totals to the bottom of forms and reports

Building a work log to track employee time

Creating a timesheet with clock in and clock out times