|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
|
||||||
|
||||||
|
Access Expert 10 Invoices as Quotes, DLookup, Calculated Fields Welcome to Access Expert 10. In this course you will learn how to set up invoices to also function as quotations, add and display paid or unpaid indicators, and use your system default table for company information. We will discuss the DLookup function for retrieving values from tables or queries, introduce calculated table fields, and cover their pros, cons, and best uses. You will also see how course content applies to different Access versions and how to participate in student forums for questions and support. Lessons
Resources
Learn More
Lesson SummaryWelcome! Invoices as Quotes & DLookup Tips - Welcome to Microsoft Access Expert Level 10. In this course we will set up invoices to also function as quotations, add paid or unpaid indicators, and continue working with system default values. We will discuss the DLookup function, which lets you retrieve values from tables or queries, and introduce calculated table fields, including their pros, cons, and when you might use them. Prerequisite knowledge from Access Expert Level 9 is recommended. We will also discuss differences for Access 2007 and earlier versions, and I will explain how this course fits into the overall series and how to use the student forums effectively.
Lesson 1: Switch Quotes & Invoices, Store Info - In Lesson 1, we will walk through adding the ability to switch between an invoice and a quotation in your database, as well as marking invoices as paid or unpaid and displaying that status. I will show you how to update your system default table to store your company information and logo, and how to use these details on forms and reports so you do not need to edit multiple places if something changes. We will also cover techniques for efficiently formatting and updating your printable quote or invoice reports to display the correct company and order status information.
Lesson 2: DLookup: Retrieve Table Data Without Forms - In Lesson 2, we will move system default values off the main menu and onto their own form, then discuss the DLookup function in Microsoft Access. I will show you how DLookup can retrieve values directly from a table without needing a form open, allowing forms and reports to access needed data efficiently. We will walk through updating forms and reports to use DLookup to grab system default values such as the sales tax rate, company name, address, and logo, and cover the basic syntax needed to apply this function.
Lesson 3: Show Related Data with DLookup - In Lesson 3, we will explore more with DLookup by adding a sales rep field to our customers and orders in Microsoft Access. I will show you how to use DLookup to display a sales rep's phone number on order forms and invoices. We will walk through configuring combo boxes for selecting both sales reps and service techs, establishing appropriate table relationships, and setting default values. We will also discuss how DLookup criteria work for different data types, including numbers, text, and dates, and how proper use of quotes and symbols is necessary for accurate lookups.
Lesson 4: Calculated Table vs Query Fields - In Lesson 4, we will discuss calculated table fields, how they differ from calculated query fields, and the problems associated with using them. We will talk about when calculated table fields may be helpful, such as with very large reports, and explain why proper database design typically avoids storing calculated values in tables. I will also show you how to create a calculated table field for tracking the number of days between an order and its ship date, and discuss compatibility issues and limitations to be aware of with calculated table fields.
Lesson 5: Invoices as Quotes & DLookup Tips - In this course we learned how to add the option for invoices to also serve as quotations, display paid or unpaid status, and set up system default values like company information using DLookup. We discussed how to use DLookup to display related information such as picking a sales rep and showing their phone number on forms and invoices. We also covered calculated table fields and their appropriate use. Finally, I shared resources for continued learning and support, and explained how to stay updated and provide feedback on the course.
NavigationKeywordsinvoices as quotes, DLookup, calculated table fields, invoice paid status, printable invoice report, store company info, DLookup syntax, sales rep lookup, combo box relationships, calculated query fields, table relationships, days between dates
IntroIn 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.TranscriptToday'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. QuizQ1. 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. SummaryToday'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 ListSetting up invoices to also serve as quotationsAdding "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 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: invoices as quotes, DLookup, calculated table fields, invoice paid status, printable invoice report, store company info, DLookup syntax, sales rep lookup, combo box relationships, calculated query fields, table relationships, days between dates PermaLink Microsoft Access Expert 10 |
