Introduction

Welcome! Order Forms with Line Items

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

Welcome to Microsoft Access Expert Level 8. In this course we will focus on building an order entry system by adding line item details and calculating totals. We will review the previous homework, work with calculated query fields to calculate extended prices and sales tax, discuss the IF and ISNULL functions, and learn about bankers rounding. We will set up tables and relationships for order details, build the subform for line items, and create the main order form to automatically calculate totals. This class builds on Access Expert Level 7 and is part of the AccessLearningZone.com expert series.

Navigation

Prev: X08
Next: Lesson 01

Keywords

Access Expert, order entry, order form, order details table, line item details, calculated query fields, unit price calculation, sales tax calculation, IIF function, immediate if, bankers rounding, subform, combo box, relationships, ISNULL function

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome 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 by two items at $10. That is $20. We are going to learn how to calculate 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, hey, 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.

Then, finally, we will put it all together. We will build an order form where you can pick a customer, set the order information, 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 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 stuff is all the same.

You are strongly encouraged to build the database that I build in class. 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 in the 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's 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, 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 table 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 little other tricks.

Quiz

Q1. What was the main focus of Microsoft Access Expert Level 8?
A. Building an order entry system with line item details and totals
B. Automating email sending with Access
C. Advanced report formatting
D. Converting Access to SQL Server

Q2. In lesson 1, what was the homework assignment reviewed?
A. Creating a pivot table
B. Showing all products supplied by a particular vendor
C. Importing data from Excel
D. Backing up the Access database

Q3. What is the purpose of using calculated query fields in this class?
A. To color code forms
B. To calculate the total for line items such as price times quantity
C. To filter records by date
D. To generate random numbers

Q4. What function is introduced for conditional logic in queries?
A. SUM
B. DLookup
C. IIF (Immediate If)
D. COUNT

Q5. Why is calculating sales tax in Access sometimes complicated?
A. Access does not support math operations
B. You must determine if an item is taxable or not
C. Access does not allow decimals
D. Queries cannot use functions

Q6. What is "bankers rounding" in Access?
A. Traditional rounding up at .5 and above
B. Rounding down always
C. A method Access uses, different from traditional rounding in high school
D. Manual rounding with a calculator

Q7. What is the best way to learn according to the instructor?
A. Watch the videos passively
B. Build the database along with the video lessons
C. Skim through the documentation only
D. Focus only on quizzes

Q8. What are the main groups Richard breaks his Access courses into?
A. Beginner, Intermediate, Professional, Master
B. Starter, User, Expert, Pro
C. Beginner, Expert, Advanced, Developer
D. Intro, Core, Power, Automation

Q9. Which table is created to store order line items?
A. Inventory Table
B. Vendor Table
C. Order Details Table
D. Payment Table

Q10. What should you do if you have a question during the course?
A. Wait until the end to ask questions
B. Only consult external books
C. Post in the student forums on the website
D. Email Microsoft directly

Q11. What is RECOMMENDED before taking Expert Level 8?
A. Watch Expert Level 7 and all previous classes
B. Only complete Level 1
C. No prior knowledge required
D. Master Visual Basic first

Q12. If an item is not taxable (like a gallon of milk), what should your query do?
A. Apply a random tax
B. Not charge sales tax
C. Charge double the tax
D. Apply only shipping cost

Q13. If using Access 2007 or 2010, what does the instructor recommend regarding following along with this course?
A. You cannot follow along
B. Most things will be different, avoid the course
C. Almost everything is the same, you should be fine
D. Only use Access 2003

Q14. What kind of box will you set up to select a product in the order details subform?
A. List box
B. Option group
C. Combo box
D. Text box

Q15. What are you encouraged NOT to do before mastering the sample database?
A. Watch any videos
B. Apply concepts to your own projects right away
C. Download the sample database
D. Participate in the forum

Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-C; 10-C; 11-A; 12-B; 13-C; 14-C; 15-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 covers Microsoft Access Expert Level 8, where we will focus on order entry. In our previous class, we began creating a simple order entry form, mainly to help with generating collection letters as part of our letter writing system. In this session, we will be expanding that order entry system by adding line item details and calculating totals.

We will start lesson 1 by going over the homework assignment from the last class. If you remember from Access Expert 7, you built a form to show a single product and all the vendors that supplied it. The assignment was to reverse this approach and create a form that displays a single vendor along with all the products they supply. We will review my solution for that task.

Next, we will discuss how to add calculated fields to queries. This is essential for order entry because we need to calculate the total amount for each line item by multiplying the unit price by the quantity. This makes sure, for example, if you have two items at ten dollars each, your total comes to twenty dollars. I will show you how to set up these calculations within your queries, including how to calculate sales tax for each line item.

A key feature you will learn is the IF function, or what Access refers to as IIF, which lets us create if-then conditions in our queries. For instance, if an item is not taxable, like milk, the system will automatically zero out the sales tax for that product. I will also explain how to round values properly, which is important to avoid fractional penny errors. You will learn about an approach Access uses called bankers rounding, which may be different from the traditional rounding rules you are familiar with from school.

By the end, we will bring everything together by building a comprehensive order form. You will be able to pick a customer, fill out the order details, use a subform for entering line items, and select products. The unit price will auto-populate, sales tax will be calculated, and the total for the order will be shown at the bottom. This will form the basis of our ongoing order entry and inventory system, which we will continue to build in future classes. If you have not yet taken Access Expert Level 7 or any of the earlier classes, I strongly recommend you do so first, as this session builds on what you have already learned. You can purchase earlier levels on my website at accesslearningzone.com.

While this course was designed for Access 2013, those using Access 2007 or 2010 should have no difficulty following along since there are very few differences between these versions. If you are using Access 2003 or earlier, you should still be able to keep up, although the menu layout and a few features may look different. Much of the content in this lesson pulls material from some of my earlier courses, and some of it is newly added to the Expert series.

For the best learning experience, I encourage you to build the database as we go along. Re-creating what I build is the best way to really learn these skills. However, if you prefer, you can download my sample database files from the website at accesslearningzone.com/databases.

My Access courses follow a four-part structure: Beginner, Expert, Advanced, and Developer. The Beginner series gives you a good overview of Access basics, the Expert series - where we are now - adds intermediate skills, the Advanced lessons handle macros, automation and other complex features, and the Developer series focuses on programming with Visual Basic in Access. Each series is broken into numbered levels so you can progress step by step.

Apart from the main courses, I also offer seminars on specific topics like building web-based databases, creating calendar forms and reports, securing your databases, working with images and attachments, writing work orders, tracking accounts payable, learning SQL, and more. You will find details about all of these on my website.

If you ever have questions while going through the course, use the student forums to post your questions and read through the answers and discussions from other students. If you are watching the course using my custom player or online theater, the forum window will appear next to each lesson as long as you are connected to the internet. You can also access the forums anytime at accesslearningzone.com/forums.

To make the most of this course, I suggest that at first you simply watch each lesson all the way through without touching your computer. Once you have an overview, go back, replay the lesson, and actually follow along while building the sample database yourself. Do not try to apply these new skills to your own projects until you feel confident working with the sample database.

If you get stuck at any point, rewatch the lesson or ask for help in the student forum. The key is to keep practicing and keep your mind open. Access may seem a bit overwhelming at first, but once you get used to it, you will find it easy to use.

Here are the specific topics we will cover today:

In lesson 1, we will review my solution to the vendor form homework and pick up a few new tricks. In lesson 2, we will continue developing our order entry system by creating an Order Details table to store line items along with a corresponding query.

Lesson 3 covers building calculated fields in queries, where you will set up calculations for unit price and quantity to determine the extended price per line item.

Lesson 4 goes over calculating sales tax using the IF (IIF) function and understanding the proper way to round numbers.

Lesson 5 focuses on building the order details subform for line items, setting up a combo box for product selection, locking the unit price field, and defining table relationships between customers, orders, and order details.

In lesson 6, we put together our main order form, learn about the ISNULL function, and go over various useful tricks.

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

Reviewing homework: vendor form showing supplied products
Building an order entry system with line item details
Calculating totals for order line items
Creating calculated fields in queries
Multiplying unit price by quantity for line item totals
Calculating sales tax for each line item
Using the IIF (Immediate If) function in queries
Conditional sales tax calculations based on product type
Properly rounding values in Access queries
Understanding bankers rounding in Access
Building an order form with customer selection
Creating a subform for order line item entry
Populating unit price automatically in subform
Automatically calculating sales tax in subform
Calculating order totals on the main order form
Setting up relationships for order and order details
Locking fields to prevent unwanted changes
Using the ISNULL function in forms

Article

Welcome to this detailed tutorial on building an order entry system in Microsoft Access. In this lesson, we are going to expand on the work from a previous class where we created a basic order form, primarily for generating collection letters as part of a letter writing system. The focus now shifts toward building a more complete order entry system, including line item details and automatic calculation of order totals.

If you have been following along with earlier work, you might recall that we previously built a form that displays a single product and lists all the vendors who supply it. As a useful exercise, we reverse that idea so that you can select a specific vendor and see all the products they supply. This is a great way to reinforce your understanding of one-to-many relationships and subforms in Access. To accomplish this, you create a main form based on your vendors table and a subform showing products, related by vendor ID or whichever key you use to link those records. Setting up the forms in this way allows you to navigate to any vendor and easily view all the products that particular vendor provides.

Next, we step into the process of handling order details. Handling line items on an order (such as individual products within a single order) means you need a separate table to track these details. Commonly, this table is called OrderDetails or something similar. Each record in the order details table will typically include fields for OrderID, ProductID, Quantity, and UnitPrice, along with calculated or data entry fields as needed.

When it comes to calculations within Access, we use calculated query fields. For example, to calculate the total amount for a line item, you might multiply unit price by quantity. If you sell two items at $10 each, the result is $20. You can do this calculation by adding a calculated field in your query like this:

ExtendedPrice: [Quantity] * [UnitPrice]

This field can be displayed on forms or reports for easy viewing.

Calculating sales tax per line item adds complexity, especially if not all items are taxable. For instance, certain items like groceries may not incur sales tax. To manage this, you can add a field to your products table indicating whether the item is taxable, such as a Yes/No field named Taxable. In your query, you use the IIF function to conditionally compute sales tax. For example, if you have a sales tax rate stored in another table or supplied as a parameter, your calculation might look like this:

SalesTax: IIF([Taxable]=True, [ExtendedPrice]*[SalesTaxRate], 0)

This formula says: if the item is taxable, multiply the extended price by the tax rate; otherwise, the tax is zero.

Another important topic is rounding. When performing calculations, especially involving sales tax or currency, always round values to two decimal places to prevent fractional penny errors. Access uses a rounding method called bankers rounding, which can differ from the standard method you learned in school. Bankers rounding rounds values ending in .5 to the nearest even number, which is important to be aware of to avoid confusion when your totals do not match manual calculations. To round in Access queries, use the Round function like this:

RoundedTax: Round([SalesTax],2)

This ensures currency values are never displayed with more than two decimals.

Once you have these calculated fields working in your query, it is time to build forms to let users enter and view orders. A typical structure is to have a main order form where you select a customer and fill in order information. Inside this form, insert a subform bound to the order details table or query, set up as a datasheet or continuous form. This subform allows users to add, remove, and update line items for the order. When a product is selected in this subform, you can have the unit price field automatically populate using a combo box that looks up the price from the products table. Lock the unit price field if you do not want the user to edit it. Use relationships in Access to link the customers table to the orders table and the orders table to the order details table, ensuring referential integrity so each order and line item stay properly connected.

As you set up your forms, you can add calculated controls in the form footer to sum up each order's details, including order subtotal, total tax, and grand total. For example, in the form footer of your subform, you can add a text box with a control source like:

=Sum([ExtendedPrice])

This box displays the sum of extended prices for the entire order.

Often, you will want to calculate the grand total, which includes the order subtotal plus any applicable sales tax. You can use similar sum text boxes with calculations, like:

=Sum([ExtendedPrice]+[SalesTax])

Sometimes you need to check if values are null, for example, to prevent errors in reports or queries. The ISNULL function comes in handy here:

=IIf(IsNull([FieldName]), 0, [FieldName])

This formula returns a zero when a field is empty or null, preventing blank entries from disrupting your totals.

This structure serves as the foundation for a fully functional order entry and inventory system. As your database grows, you can add more features, such as inventory management, accounts payable, or even web-based forms. As a best practice, always try to follow the examples step-by-step when building your database. You will learn much better by actually recreating the sample database on your own computer, ensuring you understand each step before you try to apply these techniques to a different project.

It is also worth noting that most of this material is consistent across multiple versions of Access, including Access 2007, 2010, and 2013, with only minor differences in menu appearance. The table and query designs, calculated fields, and most essential features work nearly identically in these versions.

If you want to download example databases, ask questions, or find further explanations on related topics like building web forms or reports that look like calendars, you can visit the AccessLearningZone website for additional resources. If you get stuck or need clarification, revisiting specific sections or posting questions in user forums can help you find solutions. Remember, breaking down complex projects into manageable steps, understanding the relationships between your tables, and using calculated fields effectively will make Access a powerful and easy-to-use tool for order entry applications.

To quickly recap, start by building tables for customers, orders, order details, products, and vendors with appropriate relationships. Set up calculated fields for totals and taxes in your queries, handle sales tax with IIF statements, use the Round function for currency values, and design user-friendly forms and subforms to handle order input. Employ ISNULL in queries or controls to manage null values safely. By following these principles, you will have a solid, extensible foundation for expert-level order management in Access.