|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Access Expert 3 Welcome to Access Expert 3. Total running time is 1 Hour, 26 Minutes. LessonsImportant Notice
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 UpdatesIf 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.
IntroIn lesson 3 of the Microsoft Access 2010 Expert series, you will learn the basics of SQL, how to build a callback form to manage customer follow-up calls, and how to use subforms to display related records within a main form. We will also cover creating form footer totals using functions like sum, average, max, min, and count to calculate values in your forms. These topics will help you add more functionality and flexibility to your Access databases.TranscriptWelcome 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 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 including how we built the contact form, how we built the customer form, and how we related the two together. 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 is 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 much 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 really easy to use. Now let's 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. QuizQ1. What is SQL primarily used for in Microsoft Access?A. Managing relationships between forms B. Creating and running queries behind the scenes C. Generating reports D. Designing form layouts Q2. What is the main purpose of building a callback form as described in the video? A. To track sales made to customers B. To list products available for sale C. To manage customers who need to be called back in the future D. To calculate sales totals Q3. What is a subform in Microsoft Access? A. A form used to create new tables B. A function used for data validation C. A smaller form embedded inside a larger form to show related records D. A special report preview mode Q4. Which of the following is NOT one of the popular functions you can use in form-footer totals? A. Sum B. Average C. Divide D. Count Q5. Why does the instructor recommend completing Expert Levels 1 and 2 before starting Level 3? A. They teach Visual Basic programming B. They provide foundational material such as building contact and customer forms and establishing relationships C. They focus on reporting tools only D. They introduce advanced automation techniques Q6. In what section of the form are totals like sum, average, max, min, and count typically calculated in this course? A. Form header B. Details section C. Form footer D. Navigation pane Q7. According to the instructor, what should you do if you have not been building the database along with the lessons? A. Skip to the Developer lessons B. Buy a new computer C. Download the database file from the website D. Use an unrelated sample database Q8. What is the target audience for the Expert Series of lessons? A. New users with no Access experience B. Advanced users only C. Users who are comfortable with basic Access features and want to learn more advanced topics D. Only those using Access 2013 or later Q9. How does the instructor suggest approaching each lesson for the best learning experience? A. Take notes and quiz yourself on dates B. Listen while multi-tasking with other projects C. Watch the lesson through once, then follow along step by step to build the database D. Read the textbook only, no need to watch the videos Q10. Where can students interact with the instructor and other students about the course material? A. By mailing in handwritten questions B. At the physical classroom only C. In the online student forums on accesslearningzone.com D. On unrelated programming forums Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-C; 7-C; 8-C; 9-C; 10-C 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. SummaryToday's video from Access Learning Zone is Access 2010 Expert Level 3. I'm Richard Rost, and I'm your instructor for this class.We'll start off by exploring SQL, or structured query language. This is the language that operates behind the scenes in your Access queries. Even a basic understanding of SQL gives you much more control and flexibility within your database, so we will take a few minutes at the beginning to get familiar with how it works and how you can use it to enhance your Access projects. Next, we'll move on to creating a callback form. In the previous class, we built a contact form to track interactions with customers. The new callback form will help you keep track of which customers you need to call back in the future. You'll be able to see a list of customers who are due for a call, which is especially useful for sales staff, customer service reps, and anyone who needs to manage follow-up communications. After that, we will learn about subforms. Subforms allow you to embed one form inside another. For our example, we'll create a subform to display a list of the most recent contacts for each customer, and then we will place that subform right inside the main customer form. This way, whenever you look up a customer's record, you immediately see their recent contact history. We will wrap up today by looking at form footer totals. You'll learn about the most widely-used summary functions: sum, average, maximum, minimum, and count. We'll use these to calculate totals in the footers of your forms. As an example, we'll set up a product table alongside a product category table, establish a form-subform relationship, and then use footer calculations to determine the total quantity on hand in each category as well as the overall item count. This course builds directly on what we covered in Access 2010 Expert Levels 1 and 2. In those previous levels, we discussed relational database concepts, how to normalize your tables, establish relationships, use referential integrity, and set up forms to pass values and open related records. These are fundamental skills you should have before moving forward with today's material. If you have not yet completed Levels 1 and 2, I recommend you go back and complete those first, since they lay the groundwork for the forms and structures we will be using here. For this class, I am demonstrating in Microsoft Access 2010, but everything we're covering today should also work in Access 2007 and 2013 without any issues. If you are using a much older version like Access 2003, you may want to visit my website for tutorials geared specifically to that version, since there are quite a few differences between Access 2003 and the newer releases. My Access 202 lesson covers similar material for Access 2003 users. I always encourage students to follow along and build the database themselves as we work through the lessons. It's best to construct the database step by step rather than just watch. However, if you want to download a completed version of the database I build in class, you can find it on my website. If you have been progressing through all my courses from Beginner Level 1, I recommend you continue using the same database that you've been developing all along. If you are joining the series mid-stream or have not kept up, the downloadable file is always available for you. My Access courses are organized into four series: Beginner, Expert, Advanced, and Developer. The Beginner Series covers all the core basics, including the fundamental features and interface of Access. The Expert Series, which is where this class fits in, is suited for users comfortable with Access and ready to implement more advanced techniques. Advanced lessons get into macros, automation, and more complex database features. Finally, the Developer Series explores Visual Basic programming with Access. Each series is structured into multiple numbered levels for a smooth learning progression. In addition to these courses, I offer specialized seminars targeting particular topics. These include lessons on building web-based databases, designing forms and reports that mimic calendars, adding security to your database, handling images and attachments, writing work orders, managing accounts payable, working with the SQL language, and many other subjects. You can find a comprehensive list of these seminars on my website. If you have any questions about the lessons we cover today, I encourage you to participate in the student forums. If you are watching through my custom video player software or the online theater, you'll see the relevant forum for each lesson right next to the video, provided you have an active internet connection. Here, you can see questions posted by other students, my responses to them, and additional comments from your peers. Reviewing these discussions when starting a new lesson is often helpful, and you are always welcome to post your own questions or comments. If you are not viewing the course online, you can still access the student forums at any time by visiting my website. To maximize your learning, I recommend watching each lesson all the way through without trying to work alongside me the first time. After watching, go back to the beginning, follow along step by step, and recreate the database as shown. Focus on mastering the techniques from the sample database before trying to apply them to your own projects. If you encounter any difficulties or need clarification, watch the lesson again from the start, or post your question in the student forum so I can help. And most importantly, keep an open mind. Access might appear intimidating at first, but once you get comfortable, you'll see that it's really quite user-friendly. Here's a quick overview of what we will cover today: Lesson 1 covers the basics of SQL, the language that drives your Access queries. Lesson 2 addresses the creation of a callback form, making it easy to track follow-up calls and navigate to customer records. Lesson 3 introduces subforms, letting you display related records within a main form. Lesson 4 demonstrates how to use form footers for totals, such as summing quantities in a subform and displaying the result at the bottom. For a complete step-by-step video tutorial on everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListIntroduction to SQL in Access queriesCreating a callback form for customer callbacks Listing and managing daily customer callbacks Adding a button to open customer records from the callback list Introduction to subforms in Access Embedding subforms to show related records Displaying recent contacts for each customer in a subform Setting up form footer totals in subforms Calculating sum, average, max, min, and count in form footers Setting up product and product category tables Creating a form-subform relationship for products and categories Calculating total quantity on hand in a category using form footer totals Displaying total number of items in a category via form footer totals |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access expert 03 expert 3 SELECT FROM ORDER BY WHERE Callback Query Default Value for Dates Today's Date Tomorrow's Date Tomorrow at Noon Tomorrow at 9 AM Custom Date Format Callback Form Master and Sub Forms Parent and Child Forms SQL Statement as Record PermaLink Microsoft Access Expert 3 |
