|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Access Expert 8 Welcome to Access Expert 8. Total running time is 1 Hour, 55 Minutes. Lessons
Notes
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 this lesson, you will continue building your order entry system in Microsoft Access by learning how to add line item details and calculate totals. We will start by reviewing the homework from Access Expert Level 7, then create queries with calculated fields to determine extended prices and sales tax. You will learn to use the IIf function for conditional calculations, handle rounding with Access's bankers rounding, set up an Order Details subform, and tie everything together with relationships and combo boxes for selecting products. Finally, we will build the main order form and cover additional techniques like using the ISNULL function. This is lesson 8.TranscriptWelcome to Microsoft Access Expert Level 8, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.Today's class is going to focus on order entry. We started building a simple order form in a previous class, and that was primarily so we could send out collection letters when we were building our letter writing system. In today's class, we are going to continue building on that order entry system by adding line item details and totals. The first thing that we are going to do in lesson 1 is to go over the homework assignment from the last class. If you took the previous class, Access Expert 7, you will recall that we built a form showing a single product, and then all of the vendors who supplied that product. Your homework assignment was to go in the opposite direction: show a vendor, and all of the products supplied by that one vendor. That is what we are going to see in the first lesson today. Then we are going to learn about calculated query fields in order to calculate the total for our line items. We need to be able to multiply unit price times quantity, for example, two items at $10 is $20. We are going to learn how to make calculations inside our queries. We are going to learn how to calculate sales tax for each of our line items. We are going to learn a new function called the if function, immediate if, iif. The if function allows us to perform an if-then conditional expression inside of our queries. We will use that to say, if this item is not taxable, like a gallon of milk, then do not charge sales tax. We are going to learn the proper way to round values off to prevent fractional penny errors. You are going to learn about something that Access does called bankers rounding, which is not the traditional rounding you might be used to from high school. Finally, we will put it all together. We will build an order form where you can pick a customer, set the order information, and include a subform within it that has all the line item details where you can pick a product. The unit price is automatically populated, the sales tax is calculated, and your order totals are calculated automatically on the bottom. This will be the foundation for the order entry and inventory system that we will be building throughout this series of classes. This class follows Access Expert Level 7. It is strongly recommended that you watch Level 7 and all the previous classes in the series before proceeding with today's lessons. To purchase a copy of Level 7, visit my website at accesslearningzone.com. This class was designed to be used with Access 2013. If you are using 2007 or 2010, you should not have any problems following along as the three versions are almost identical. If you do happen to come across something that is significantly different, please email me and let me know. If you are using Access 2003 or earlier, you should still be able to follow along with the lessons covered today. Most of the material is pretty much the same. There is no exact match for this class in my older lessons. The material that I am covering today has come from several different lessons and there is some new stuff added in. I used to not cover order entry at all until my advanced lessons, Access 301. However, now I have decided to move some of the material into the expert series. This is kind of a patchwork of material covered from various other classes, but you should be able to follow along. The menus are a little different, but the rest of the material is all the same. You are strongly encouraged to build the database that I build in class, as that is the best way to learn. However, if you are lazy like me, go ahead and download it from my website at accesslearningzone.com/databases. 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. 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 have mastered 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 us take a closer look at exactly what is covered in today's class. In the last class, Access Expert 7, we built a form with a subform so we could see the product and all of the vendors we purchased that product from. Our homework for today was to reverse that, to build a vendor form where we can see all of the products that vendor supplies. In lesson 1, we will review my solution for the homework and we will learn a few new things as well. In lesson 2, we are going to do some more work on our order entry system. We are going to create a table to track line items called the Order Details table and a query to go with it. In lesson 3, we are going to learn how to create calculated query fields. We will take the unit price and multiply it by the quantity to get the extended price for that line item. In lesson 4, we are going to learn how to calculate sales tax. We are going to learn about the IF function and we are going to learn how to properly round numbers. In lesson 5, we are going to build our order details subform, the line items that you will see on the order form. We will set up a combo box to pick a product, lock the unit price so the user cannot change it, and we will set up the relationships between our customer and order tables and also our order and order details tables. In lesson 6, we are going to set up our main order form. We will learn about the ISNULL function and lots of other little tricks. QuizQ1. What is the primary focus of Microsoft Access Expert Level 8?A. Building queries to generate reports B. Order entry with line item details and totals C. Designing macros for automation D. Securing access to databases Q2. In lesson 1, what homework solution is reviewed? A. Showing all customers for a product B. Showing all products for a vendor C. Adding passwords to a database D. Creating mailing labels Q3. What is the purpose of calculated query fields in this class? A. To create new tables from queries B. To allow for conditional form formatting C. To perform calculations such as unit price times quantity D. To automatically generate primary keys Q4. What function is introduced to handle conditional expressions in queries? A. DMax B. IFERROR C. IIF (Immediate If) D. VLOOKUP Q5. What is 'bankers rounding' as discussed in the class? A. Rounding towards zero for all values B. Rounding down to the nearest integer C. A special Access rounding method that differs from traditional rounding D. Rounding up at .5 every time Q6. Which of the following is included in the order entry form built in this class? A. Automatic product code generation using VBA B. Populating unit price, calculating sales tax, and displaying order totals C. Inventory forecasting using external data sources D. Automatic emailing of order confirmations Q7. Why is it recommended to build the database along with the class? A. To speed up the class B. It is the only way to access the course forum C. It is the best way to learn by doing and reinforce the concepts D. So you can use the database for your company immediately Q8. What is the correct approach to learning from this course? A. Skip to the quizzes and avoid videos B. Only watch the videos without doing any practice C. Watch each lesson fully once, then follow along to build the sample database step by step D. Apply the concepts immediately to unrelated business projects Q9. The content of Access Expert Level 8 is best suited for: A. Complete beginners with no Access experience B. Advanced VBA developers only C. Users already comfortable with Access, looking to advance their skills D. People only interested in web database development Q10. If you have questions about the material, what should you do? A. Contact support via phone only B. Post in the course's student forums where you can see others' questions and answers C. Wait until the end of the course for a Q&A session D. Post questions on unrelated social media sites Q11. Which Access versions are compatible with the Expert Level 8 material? A. Only Access 2013 B. Access 2021 and newer only C. Access 2007, 2010, 2013, and mostly earlier versions D. Access for Mac only Q12. What is the use of the ISNULL function mentioned in lesson 6? A. To generate random numbers in queries B. To count the number of records in a table C. To check if a field has no value D. To sort data alphabetically Q13. What new table will be created as part of the order entry system in this course? A. Vendor Payment table B. Order Details table C. Product Returns table D. Employee Timesheet table Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-B; 7-C; 8-C; 9-C; 10-B; 11-C; 12-C; 13-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 is part of Microsoft Access Expert Level 8, and our main focus is on extending the order entry system. In previous lessons, we started a basic order form. That original form was used mainly for sending out collection letters as part of our letter writing system, but now we're going to build it out by adding line item details and automatically calculated totals.We begin in lesson 1 by reviewing the homework assignment from Access Expert 7. Last time, we built a form that displayed a single product along with all the vendors who supply it. Your assignment was to create the reverse: show a vendor and list all the products that vendor provides. In our first lesson, I'll walk through my solution to that task and highlight a few additional tips. Next, I'll introduce calculated query fields so we can compute totals for each of our line items. It's essential to be able to calculate values like multiplying the unit price by the quantity, so if you order two units at ten dollars each, your total for that line is twenty dollars. I'll demonstrate how to make these calculations inside your queries. In addition, you'll see how to calculate sales tax for each individual line item. A key part of today's class is learning how to use the Immediate If (IIf) function. This is Access's way of handling "if-then" logic directly within your queries. For example, if a product is not taxable, like a gallon of milk, we can set things up so that no sales tax is charged for such items. I'll also cover the correct way to round values to avoid errors from fractional pennies. Access uses banker's rounding, which behaves differently compared with the rounding you might remember from school. After building these components, we'll put everything together. You'll see how to create a complete order form where you can select a customer, enter order details, and work with a subform listing your line items. As you pick products, the unit price will fill in automatically, sales tax will be calculated, and all your order totals will update at the bottom of the form. This updated order entry form sets the groundwork for the inventory and sales system we'll be developing throughout this series. This class continues directly from Access Expert Level 7, so I strongly recommend watching Level 7 and all previous parts in this series to make sure you're up to speed. If you need a copy of Level 7, you can get it at my website. Although the course was designed for Access 2013, users of Access 2007 or 2010 should have no difficulty following along since all three versions are nearly identical. If anything major is different, feel free to let me know. For those using Access 2003 or earlier, you'll still be able to follow most of the material, although the menu locations may vary, and this set of lessons compiles material from several prior versions along with some new content. In older classes, order entry wasn't covered until the advanced series (Access 301), but now that content is included here. I always encourage you to build the database alongside me as we move through the lessons. That hands-on experience is the most effective way to learn. However, if you prefer, you can download the sample database from my site. My courses are organized in four tracks: Beginner, Expert, Advanced, and Developer. The Beginner series gives you a basic understanding of the simplest Access features. The Expert track, which you're watching now, is meant for users who already have a good handle on Access. The Advanced series covers topics like macros and automation, and the Developer series will get into programming in Visual Basic for Access. I also offer seminars on specialized subjects, such as building web-based databases, creating calendar-style forms, setting up database security, handling images and attachments, designing work order systems, tracking accounts payable, learning SQL, and many others. Details on all of these can be found on my site. If you have questions about today's topics, I encourage you to post them in the student forums. When using my custom video player or the online theater on my website, you'll see the student forum for each lesson displayed next to the video as long as you have an internet connection. Here you'll find my responses and the discussions from other students. Be sure to review these questions and answers as you begin each lesson and add your own where needed. If you aren't online during the lessons, you can visit the forums at any time on my website. To really get value from this course, I recommend first watching every lesson from beginning to end without interruption. Afterward, go back and follow the examples on your computer, building the same database from scratch. Avoid trying to adapt the concepts to your projects until you have successfully completed the sample database as shown in class. If you encounter issues or something is unclear, rewatch the lesson or ask in the forums. Most importantly, keep an open mind. Access can seem a bit daunting at first, but you'll find it much easier with some practice. Here's a quick overview of what we'll cover today: Lesson 1 reviews the last class's assignment: building a vendor form showing all products that a vendor supplies, along with my solution and a few extra insights. In lesson 2, we'll add more to our order entry system by creating a table to track order line items—the Order Details table—and a corresponding query. Lesson 3 introduces calculated fields in queries to get extended prices by multiplying the unit price by quantity. In lesson 4, we look at calculating sales tax, how to use the IIf function, and the right way to round numbers. Lesson 5 is about building the order details subform. Here you'll learn to set up a combo box to pick a product, lock down the unit price, and establish relationships between the customer and order tables, as well as between the order and order details tables. In lesson 6, we'll finalize our main order form and cover useful functions like IsNull, plus various tips and tricks. A complete video tutorial with step-by-step instructions for everything discussed here is available on my website at the link below. Live long and prosper, my friends. Topic ListReviewing vendor form with all supplied productsCreating an Order Details table for line items Building a query for order details Creating calculated query fields for line totals Multiplying unit price by quantity in queries Calculating sales tax for line items Using the IIF function for conditional tax calculation Rounding values to prevent fractional penny errors Understanding bankers rounding in Access Building the order details subform Setting up a combo box to select products Locking the unit price in the subform Establishing relationships between customer and order tables Establishing relationships between order and order details tables Creating the main order form with subform Using the ISNULL function in forms Calculating and displaying order totals automatically |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access expert 08 expert 8 Products Vendors HomeworkReview ButtonOpen Forms VendorFVendorProductSubF ButtonQuery UnitPrice Junction VendorXProductWithProfitQ AddPrice Profit SubF OrderDetails OrderDetailT DeleteAmountDue CreateOrderDetailQ CalculateExtPric PermaLink Microsoft Access Expert 8 |
