Introduction

Welcome! SQL, Callback Forms, Subforms, Totals

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Access Expert Level 3. In this course we will introduce SQL and discuss how understanding structured query language can provide more power and flexibility in your databases. We will build a callback form to track customers you need to contact, and learn how to create and use subforms to show related records within a main form. We will also cover how to calculate form footer totals using functions like sum, average, max, min, and count. Prior experience with Access Expert Levels 1 and 2 is recommended to understand the relational concepts and forms we build on in this class.

Navigation

Prev: X03
Next: Lesson 01

Keywords

Access Expert, SQL, queries, subform, callback form, form footer totals, sum function, average function, max function, min function, count function, product table, product category table, relational database, cascade updates, referential integrity

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Access 2010 Expert Level 3, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class is going to begin by teaching you a little bit about SQL, that structured query language. It is the language behind the queries in your database. Knowing how to work with just a little tiny bit of SQL gives you a lot of extra power and flexibility in your databases. So we will spend a few minutes going over some SQL.

Next, we are going to build a callback form. In the last class, we built a compact form, where we tracked previous contacts with our customers. A callback form is going to allow us to set a list of customers that we have to call in the future. We will be able to quickly pull up a list of all the customers that we have to call for the day. This is good for sales reps, customer service contacts, and so on.

Then, we are going to learn about something very powerful called a subform. This is where you can take one form and embed it inside of another. In this example, we will make a small subform to show a list of the last couple of contacts for each customer, and we will embed that right inside of the customer form. When you open up a customer's record, you will see the last couple of contacts for them.

Finally, we will learn about form-footer totals. We will learn about the five popular functions: sum, average, max, min, and count, and how you can use them to calculate totals in the footers of your form. We will set up a product table and a product category table. We will make a form, subform relationship out of that, and we will set up form footer totals to calculate the quantity on hand for all the units in a product category, and the total number of items.

This class follows Microsoft Access 2010 Expert Level 2. In Levels 1 and 2, we covered relational concepts, normalization, working with multiple tables, referential integrity, cascade updates and deletes, setting values across forms, and opening up the contacts form for the current customer. All of these things are very important before you begin today's class.

If you take today's class without having taken Levels 1 and 2 of the Expert Series, you will be missing a lot of the foundation material and how we built the contact form, how we built the customer form, and how we related the two together. So I strongly recommend you watch Levels 1 and 2 first.

I am using Microsoft Access 2010 in this class. The topics that I cover should work just fine in Access 2007. You should not have any problems following along. If you are using 2013, all the lessons today should work just fine as well. If you are using Access 2003 or earlier, you should either upgrade your version of Access or visit my website and look for my Access 2003 tutorials. Access 2003 is pretty different from 2007 and 2010. The course that is the equivalent of today's class is my old Access 202 lesson for Access 2003.

I strongly encourage you to build the database that I actually build in class, follow along with me in the videos, and build it yourself. However, if you would like to download a copy of my database file, you will find it at accesslearningzone.com/databases.

If you have been with me since Access Beginner 1, it is best if you use the copy of the database that you have been working on since then. However, if you jumped in midstream or if you have not been building the database with me, you can always download the file there.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer. The Beginner lessons are designed to give you a basic overview of the simple features of Access.

The Expert series, the one you are in now, is designed for more experienced users who are already comfortable with Access. The Advanced lessons cover working with macros, automation, and many more advanced features. The Developer lessons get into programming with Visual Basic for Microsoft Access. Each of the series are broken down into different numbered levels, starting with Level 1. The Beginner series, for example, had Levels 1 through 9. This is Level 3 of the Expert series.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders, tracking accounts payable, learning the SQL programming language, and lots more. You can find complete details on all these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos, as long as you have an active internet connection. Here, you will see all of the questions that other students have asked, as well as my responses to them, and comments that other students may have made. I encourage you to read through these questions and answers as you start each lesson and feel free to post your own questions and comments as well.

If you are not watching the lessons online, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend that you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you master the sample database from this class. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum.

Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you will see that it is real easy to use.

Now let us take a closer look at exactly what is covered in today's class.

In lesson 1, we are going to learn a little bit about SQL, structured query language. It is the language behind all the queries in your Access databases.

In lesson 2, we are going to create a callback form. A callback is simply a contact where you have indicated you want to call the customer back at some point in the future. We will make a list of all of our callbacks plus a button to open that customer's record.

In lesson 3, we are going to learn about something that is very powerful in Microsoft Access called a subform. This is where you can show related records by embedding a smaller form inside of a larger form.

In lesson 4, I am going to show you how to create form footer totals. For example, adding up all the quantities in a subform and putting that value in the subform's footer.

Thank you.

Quiz

Q1. What is SQL in the context of Microsoft Access?
A. It is the language behind the queries in your database
B. It is a feature for formatting forms
C. It is only used for creating macros
D. It is a reporting tool

Q2. What is the main purpose of building a callback form as described in this lesson?
A. To keep a record of deleted customers
B. To schedule and track future calls to customers
C. To record sales invoices
D. To secure the database

Q3. What is a subform in Access?
A. A form used only for entering customer addresses
B. A smaller form embedded inside another form to display related records
C. A special type of report
D. A database backup tool

Q4. What are form footer totals used for?
A. To print out each single record
B. To calculate aggregate values like sum, average, max, min, and count for items in a form or subform
C. To delete all records in a form
D. To save forms in different formats

Q5. Why is it recommended to watch Expert Levels 1 and 2 before taking Expert Level 3?
A. Levels 1 and 2 include the necessary foundation topics and database structures needed for this class
B. You will get a certificate only after completing Levels 1 and 2
C. Level 3 is only about theory, not practice
D. Level 3 does not require any prior knowledge

Q6. Which Access versions are compatible with the lessons in this video?
A. Only Access 2010
B. Access 2007, 2010, and 2013
C. Access 2003 or earlier only
D. Only Access 2013 and later

Q7. What does the instructor suggest you do after watching each lesson?
A. Take a break and do nothing
B. Replay the lesson and build the same database step by step
C. Apply the concepts instantly to your own projects
D. Only read the documentation

Q8. Where can you download the sample database files used in these lessons?
A. From accesslearningzone.com/databases
B. From Microsoft official website
C. Only from the Access 2010 CD
D. From a third-party site

Q9. What is the recommended approach if you have questions about lesson content?
A. Contact instructor by phone only
B. Use the student forums provided with the lessons
C. Email the instructor directly with each question
D. Wait until the next class to ask questions

Q10. What is the benefit of reading through the student forum before starting a lesson?
A. To get official certification faster
B. To see questions and answers from previous students, which may help you understand better
C. To download additional software
D. To skip parts of the video

Answers: 1-A; 2-B; 3-B; 4-B; 5-A; 6-B; 7-B; 8-A; 9-B; 10-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 is Expert Level 3 for Microsoft Access 2010. I am Richard Rost, your instructor for this course.

We start by looking at SQL, or Structured Query Language, which is essentially the foundation behind all of the queries in your Access database. Even just a basic understanding of SQL can give you much greater control and flexibility when building your databases, so we will begin the class by going over the basics.

After our introduction to SQL, I will show you how to create a callback form. If you joined me for the previous class, you will remember that we built a contact form to keep track of previous communications with customers. Now, we will expand on that by setting up a callback form. This form lets us mark customers who need to be called back in the future, and we will develop a system that allows you to quickly see all the customers you need to contact on any given day. This is especially helpful if you work in sales or customer service.

Next, I will explain the concept of subforms within Microsoft Access. A subform allows you to embed one form inside another. For example, you can use a subform to display the last few contacts for a given customer right inside that customer's main record. This way, when you open a customer, you can immediately see their most recent contact history.

We will also go over form footer totals. I will teach you how to use five common aggregate functions - sum, average, max, min, and count - to calculate totals in your form's footer. For our exercise, we will set up a product table alongside a product category table, build a form with a related subform, and configure footer totals to show the total quantity on hand for all items in a category as well as the total number of items.

This course builds directly on Access 2010 Expert Level 2. In the previous levels, we covered relationships, table normalization, working with multiple tables, setting up referential integrity, cascade updates and deletes, passing values between forms, and how to open related forms, such as the contacts form for the current customer. You really want to be comfortable with these topics before starting this class.

If you have not already completed Levels 1 and 2 of the Expert Series, I strongly suggest you go back and work through those before attempting Level 3. Otherwise, you will be missing vital foundation pieces like how we constructed the customer and contact forms and how those are related.

Throughout this course, I am using Microsoft Access 2010. If you are using Access 2007, everything should work the same. If you are on Access 2013, the lessons will also still apply. However, if you are using Access 2003 or earlier, I recommend upgrading or, if that is not possible, check out my Access 2003 tutorials, especially my Access 202 lesson, which covers the equivalent material.

I highly encourage you to build your own database step by step as you follow along with the lessons. You will get the most out of the course by creating everything yourself as you see it in the videos. If you would like to download my database file, you can find it at accesslearningzone.com/databases.

If you have been following from Beginner Level 1, it is best to use the copy of the database you have been building along the way. But, if you jumped in at this level or have not been creating the same database, you can always download a copy from the website.

My Access courses are broken down into four series: Beginner, Expert, Advanced, and Developer. The Beginner lessons cover all the basic features and concepts. The Expert series, which you are watching now, is for those who already feel comfortable with the basics and want to expand their skills further. The Advanced lessons deal with topics like macros and automation, and the Developer series focuses on programming using Visual Basic for Applications. Each series is divided into numbered levels, starting with Level 1. For example, the Beginner series has nine levels. This is the third level of the Expert series.

I also offer seminars that go into specialized topics, such as building web-based databases, designing calendar-style forms and reports, database security, working with images and attachments, managing work orders, tracking accounts payable, learning SQL in depth, and much more. You will find details on all available seminars at accesslearningzone.com.

If questions come up while taking these lessons, you are welcome to ask them in my student forums. If you are using my custom video player or the online theater on my site, the student forum for each lesson will appear in a window next to the video as long as you are connected to the internet. Here, you can see questions other students have posted, my answers to them, and discussions between students. I suggest reading through these before each lesson, and feel free to contribute your own questions or comments.

If you are not watching online, you can still access the forums anytime by visiting accesslearningzone.com/forums.

To get the most benefit from the course, I recommend first watching each lesson all the way through without attempting anything on your computer. After that, go back to the start of the lesson and follow along step by step, building the same sample database that I show. Focus on getting the class example working before you try to apply the concepts to your own projects. If something is unclear, rewatch the video or ask for help in the forum.

Keep an open mind as you learn. Access can seem a bit daunting at first, but as you get accustomed to it, you will see that it is actually quite user-friendly.

Let me give you a quick overview of what is in today's class:

Lesson 1 covers the basics of SQL, which is the language underpinning every query in Access.

Lesson 2 shows you how to create a callback form for keeping track of which customers you need to call back. We will create a complete list of callbacks and add a button to quickly open the corresponding customer's record.

Lesson 3 introduces subforms, which let you display related records by embedding a smaller form within a larger one.

Lesson 4 demonstrates how to create form footer totals, such as summing quantities in a subform footer.

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

Introduction to SQL in Access
Building a callback form
Listing customers to call back
Adding buttons to open customer records
Creating and using subforms
Embedding subforms in main forms
Viewing related contacts in subforms
Creating form footer totals
Using sum, average, max, min, and count functions
Setting up product and product category tables
Form and subform relationships for products
Calculating subform footer totals
Displaying total quantities and item counts in forms

Article

Welcome to Access 2010 Expert Level 3. In this tutorial, we are going to take your Microsoft Access knowledge to the next level by exploring some important and practical features that will help you build more powerful databases. We will cover the essentials of SQL, how to build a callback form, how to work with subforms, and how to calculate totals in form footers.

First, let us spend some time learning about SQL, or Structured Query Language. SQL is the language that runs behind the scenes of all your queries in Microsoft Access. Understanding a few SQL basics gives you more power and flexibility when working with queries. For example, when you use the query designer in Access, it actually writes SQL code for you. If you want to do more advanced things, you can switch from Design View to SQL View to see and edit that code directly. For instance, a simple SQL statement to select all customers from a table would look like this:

SELECT * FROM Customers;

You can add criteria, sorting, and more using SQL. Suppose you want to see all customers from New York. You can write:

SELECT * FROM Customers WHERE State = 'NY';

Knowing how to read and modify SQL statements in Access will help you customize queries beyond what the graphical tools offer.

Moving on, let us build a callback form. In a previous lesson, we created a contact form to track interactions with customers. A callback is a special type of contact where you have indicated you want to call the customer back at some point in the future. Imagine you have a customer who requests a follow-up next Tuesday. By marking this in your database, you can easily keep track of such callbacks.

To create a callback form, begin by adding a field to your contact table to record the callback date. This could be called CallbackDate. Then, build a query that filters for contacts whose CallbackDate is today or in the future. This will give you a list of all callbacks you need to make. To make it even more useful, build a form based on this query. On the form, add a button next to each record that allows you to open the specific customer's main record for more information. To create such a button, you can use a macro or VBA code like this:

DoCmd.OpenForm "CustomerForm", , , "CustomerID = " & Me.CustomerID

Replace CustomerForm with the name of your main customer form. This setup is especially useful for sales representatives or customer service staff who need a quick way to access customers who are awaiting a callback.

Next, let us explore subforms, a very powerful feature in Microsoft Access. A subform is a form embedded inside another form. This is particularly useful for displaying related records. For example, you could have a main form showing customer details and a subform showing the last few contacts for that customer. When you open a customer's record, you immediately see all recent interactions.

To set this up, design your main customer form as usual. Then create a separate form (the subform) that displays contact records. On the customer form, add the subform object and link it to the main form using the CustomerID field. Access will automatically handle the linking if you set it up properly. As a result, as you browse through customers on the main form, the subform will automatically display only their related contact records.

Subforms are not limited to contacts and customers. Suppose you have products and product categories. The main form can be set up for categories, and the subform to list all products in each category. This is a flexible way to represent any one-to-many relationship in your database.

Now let us talk about form footer totals. Sometimes, you want to see sums, averages, or counts of data shown in a subform or on a form. Access makes this easy with some built-in functions. In the form or subform footer, add a textbox and use an expression to calculate the total you need. For instance, to add up all quantities in a subform, your textbox Control Source can be:

=Sum([QuantityOnHand])

Similarly, you can use other functions like:

=Avg([Price])
=Max([OrderDate])
=Min([OrderDate])
=Count(*)

This approach is very useful for quick reporting. For example, if you have a subform listing all products in a selected category, you might want to show the total number of items in that category, the average price, or the maximum and minimum prices. Just place the calculation textbox in the footer of the subform and it will always show totals based on the records currently displayed.

When creating a system like this, it is important to have a well designed database. In the earlier stages you should already understand concepts like relational databases, normalization, working with multiple tables, referential integrity, and so on. This ensures your tables are related properly, especially when setting up subforms or working with SQL queries.

Although we are focusing on Access 2010 in this tutorial, most of the lessons here apply to Access 2007 and 2013 as well. Users of Access 2003 or earlier should be aware that those versions are quite different, so if possible, use Access 2007 or newer to follow along with the examples here.

It is very helpful to build the sample database as you read through this tutorial. Try out each step for yourself, starting from a blank database if necessary, though using a real example makes learning much easier. Focus on mastering these concepts within the sample database before you try to apply them to your own projects.

If you have questions as you work through this material, it is a good idea to join an online forum or community for Access learners. Reading other students' questions and answers often helps clarify concepts, and you can also share your own experiences and ask for help if you get stuck.

In summary, we have covered how to use SQL in Access to build and edit queries directly, how to make a callback form to keep track of customers who need follow-up, how to embed subforms to show related records conveniently, and how to include calculated totals in your forms. Mastering these features will greatly improve your database applications and workflow in Microsoft Access. Take your time to practice each concept, and soon you will be building smarter, more dynamic Access solutions.