Lessons

Resources

Learn More

Multiple Joins: https://599cd.com/MultipleJoins

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.

Comments for Access Expert 10

Age	Subject	From
13 years	Error	John Miller
13 years	Does MYSQL Oracle or Microsoft SQL server have o	Bruce Reynolds
13 years	Multidimensional Databases	Bruce Reynolds
13 years	Calculated fields in a table	Bruce Reynolds
13 years	Excel has a CONCATENATE function	Bruce Reynolds
13 years	Quotes and double quotes	Bruce Reynolds
13 years	Spaces	Bruce Reynolds
<< Previous

Subscribe to Access Expert 10
Get notifications when this page is updated

Intro

In lesson 10 of the Microsoft Access Expert series, we will enhance our invoices by allowing them to double as quotations and add a paid or unpaid status to each one. We will build on our use of system default values, set up a dedicated form for managing them, and learn how to use the powerful DLookup function to automatically pull information such as a sales rep's phone number into forms and invoices. Finally, we'll compare calculated table fields and calculated query fields in Microsoft Access, discussing when and why you should (or should not) use them.

Transcript

Today's class focuses on three main topics.

First, we'll set up our invoices so they can also be quotations. We'll also put "paid" or "unpaid" on the top of each invoice. We'll do some more work with those system default values that we started working with in the last class.

Next, we'll learn about one of the more powerful functions in Access called DLookup. This function allows you to look up a value from a table or query. For example, in the image here, you can see I can select a sales rep for an order, and the DLookup function will automatically display that sales rep's phone number.

Finally, we'll take a look at calculated table fields as opposed to calculated query fields. Calculated table fields allow you to put calculations directly into your tables instead of putting them in queries. There are some pros and cons, but we'll discuss them in today's class.

The prerequisite for this class is Access Expert Level 9. The printable invoice that we've built in Level 9 will be used in today's class. So if you haven't taken Level 9, I strongly recommend you take it before taking today's class.

Today's class was designed to be used with Access 2013. If you're using Access 2010, this lesson will also be fine for you. Most of the material in today's class will work just fine with Access 2007 as well, except the lesson on calculated table fields. It's a very short lesson, and I really don't recommend using calculated table fields at all for most purposes, so you're really not missing much. The last lesson, lesson 4 on calculated table fields, is not valid for Access 2007, because that feature was added in 2010.

The bottom line is don't worry. If you're using Access 2007, you're not missing anything. If you're using Access 2003 or earlier, you should be able to follow along with lessons 1 through 3: the quota invoice, the DLookup functions. There's no exact match for this class because I didn't use to cover these topics until my advanced lessons, and the material in today's class comes from 3 or 4 different lessons from the Access 2003 series.

As I've revised the course, I've decided to move some things around and change the order in which some things were displayed. So this class is a combination of a few different lessons from my Access 2003 series.

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.

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're 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're 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. Don't try to apply what you're learning right now to other projects until you master the sample database from this class.

If you get stuck or don't understand something, watch the video again from the beginning or tell me what's 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'll see that it's really easy to use.

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

In the last class, Access Expert 9, we developed a printable invoice that we could generate off of our order form. In today's class, we're going to add the ability to switch between an invoice and a quotation. If it's an invoice, we will mark whether it's paid or unpaid and display that information right on top of the quota invoice. In addition, we're going to take our company name, PCRESHELL.net, and add that information to the system default table.

In lesson 2, we're going to move the system default values off the main menu onto its own form. Then we are going to learn about one of the most powerful functions in Access called DLookup, where you can look up a value directly out of a table without having to have the form open.

In lesson 3, we're going to spend some more time working with the DLookup function. We're going to add a sales rep field to our customers and our orders. By picking the sales rep, I'm going to use DLookup to display that sales rep's phone number on the order form and on the invoice.

In lesson 4, we're going to discuss calculated table fields, how they're different from calculated query fields, what the problems with them are, why you generally shouldn't use them, and the exception when it's okay to use them.

Quiz

Q1. Which feature will be added to invoices in today's class?
A. The ability to email them directly
B. The ability to also use them as quotations and display "paid" or "unpaid"
C. Automatic tax calculation
D. Automatic archiving

Q2. What is the primary function of DLookup in Microsoft Access?
A. To create new records automatically
B. To look up a value from a table or query without opening the form
C. To format database reports
D. To sort query results

Q3. What is the main difference between calculated table fields and calculated query fields discussed in this class?
A. Calculated table fields store calculations directly in the table, while calculated query fields do not
B. Calculated query fields require VBA code
C. Calculated table fields are always more efficient
D. Calculated query fields cannot use arithmetic operations

Q4. For which version of Access is the lesson on calculated table fields not valid?
A. Access 2016
B. Access 2010
C. Access 2007
D. Access 2013

Q5. What do the system default values control in this Access database project?
A. Printer settings
B. Default financial year
C. Pieces of company information like name and other global settings
D. Macro security level

Q6. What is recommended before starting this class?
A. Having completed Access Expert Level 9
B. Owning Office 365
C. Installing third-party add-ons
D. Familiarity with SQL Server

Q7. If you are using Access 2007, what should you know about the material in this lesson?
A. You will not be able to follow any part of the lessons
B. Only the lesson on calculated table fields is not applicable
C. You must upgrade to Access 2013
D. All lessons are fully compatible

Q8. How should students approach learning to get the most out of this course?
A. Try out everything on their own databases immediately
B. Memorize all the lesson text first
C. Watch each lesson once through, then replay and build the sample database step-by-step
D. Only read the forum comments

Q9. What is the purpose of moving system default values off the main menu onto their own form?
A. To allow them to be printed easily
B. To organize and maintain system defaults more efficiently
C. To make the database more secure
D. To hide company information from users

Q10. Where can students interact with the instructor and other learners about lesson content?
A. Via personal emails
B. In the student forums on the website
C. Only in live Zoom sessions
D. Through postal mail

Answers: 1-B; 2-B; 3-A; 4-C; 5-C; 6-A; 7-B; 8-C; 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 focuses on three main topics that build on what we started in the last class.

First, I will show you how to set up your invoice system so that it can also function as a quotation system. You will be able to mark each invoice as either paid or unpaid, and that status will appear at the top of the document. We will continue working with system default values, expanding on what we did before.

The next topic is the DLookup function, which is one of the more powerful tools in Microsoft Access. DLookup allows you to retrieve values from tables or queries. For example, you could select a sales representative for an order, and then use DLookup to automatically display that person's phone number in your form.

The third topic covers calculated table fields versus calculated query fields. Calculated table fields let you place calculations directly in your tables instead of relying on queries. There are some advantages and disadvantages to this method, and we will cover both sides during the lesson.

Before jumping in, you should have completed Access Expert Level 9 since the printable invoice we built in Level 9 will be essential for the exercises in today's class. If you have not already watched Level 9, I strongly recommend completing it first.

This course was created with Access 2013, but the content is also suitable for those using Access 2010. Most of the material will work fine in Access 2007, too, except for the segment on calculated table fields, which is a short section and generally not recommended for most scenarios anyway. Calculated table fields were introduced in Access 2010, so that piece will not apply to Access 2007 users. If you are on Access 2007, do not worry; you will only miss a small part. If you are using Access 2003 or earlier, the first three lessons, such as managing quotes, invoices, and DLookup, will still be helpful. In fact, this class combines content pulled from several different advanced lessons from my older Access 2003 series.

Over time, I have reorganized the Access curriculum. As a result, today's class merges ideas from various levels of past courses.

Let me briefly explain how my courses are structured. The lessons are grouped into Beginner, Expert, Advanced, and Developer series. Beginner lessons cover the fundamental features of Access. The Expert courses, including the one you are currently watching, are designed for users who are already comfortable with Access and want to move into more challenging territory. The Advanced series explores complex features like macros and automation, while the Developer series teaches programming with Visual Basic for Access.

Each series is divided into numbered levels, beginning with Level 1. The Beginner series, for instance, runs through Levels 1 to 9.

In addition to the standard classes, I also offer specialized seminars on topics like building web-based databases, designing calendar-style forms and reports, database security, managing images and attachments, handling work orders, tracking accounts payable, learning SQL, and much more. You can find all the details about these seminars and more on my website at accesslearningzone.com.

If you have questions about any part of today's lessons, I encourage you to post in the student forums. If you are using my video player software or watching in the online theater on my website, the forum for each lesson should appear in a window next to the video, provided you are online.

You can view questions and answers from other students, see my responses, and join in by posting your own questions and comments. Even if you are not watching online, you can visit the student forums at accesslearningzone.com/forums.

To get the most out of the course, I suggest you first watch each lesson in full without attempting to follow along on your computer. After that, go back and replay the lesson, this time working through each step in your own database as I do in the video. Make sure to duplicate the demonstration database before applying these concepts to other projects. This approach will help you master the ideas before moving on.

If you find yourself stuck, rewatch the relevant video, or ask a question in the student forum. Most important of all, keep a positive attitude. At first, Access can seem daunting, but as you practice, you will grow more confident.

Let's go over the specific content covered today.

In Access Expert Level 9, we developed a printable invoice that could be created from our order form. Today, we will add functionality so you can switch between an invoice and a quotation. For invoices, you can mark them as paid or unpaid, and display that status at the top. We will also update the system default table to include the company name, PCRESHELL.net.

In the next lesson, I will move the system default values off the main menu and onto their own form. Then, I will introduce the DLookup function, showing how to look up data directly from a table even if the relevant form is not open.

The third lesson goes deeper into DLookup. I will add a sales rep field to both customer and order records. By selecting a sales rep, DLookup will display the correct phone number on both the order form and the invoice.

Finally, the last lesson will cover calculated table fields. I will explain how they differ from calculated query fields, why they are generally not recommended, and in what rare situations it might be acceptable to use them.

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

Setting up invoices to also serve as quotations
Adding "paid" or "unpaid" status to invoices
Displaying payment status at the top of invoices
Adding company name to system default table
Moving system default values to a separate form
Using DLookup to retrieve values from tables
Adding a sales rep field to customers and orders
Displaying a sales rep's phone number using DLookup
Explaining calculated table fields versus query fields
Discussing pros and cons of calculated table fields
Identifying exceptions for using calculated table fields