Lessons

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 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.

Subscribe to Access 311
Get notifications when this page is updated

Intro

In lesson 311, we will work in Microsoft Access to add unit cost to products on our orders so we can track costs and calculate net profit, create a sales report and form to display monthly sales totals with the ability to specify a date range, and build a dynamic SQL form where users can filter records such as invoices or quotes using a combo box that updates the form data automatically.

Transcript

Welcome to Microsoft Access 311 brought to you by accesslearningzone.com. I am your instructor Richard Rost.

In today's class, we are going to focus on a couple of different topics. First, we are going to add unit cost to our orders, meaning that we are going to add a cost to each of our products so that we can track our cost on each sale, for example, and we will be able to generate our net profit.

Then we are going to create a sales report and form. The sales report will allow us to show all of our sales broken down by month, with totals for each month. We will also make a form so we can specify a range, such as, show me all the sales between January and March of 2007, for example.

Next, we are going to get into something neat. It is called a dynamic SQL form. Basically, what this means is I want to be able to change the records that are in the form based on some criteria. That criteria could be listed in a combo box, for example, like show me all the invoices, show me all the quotes, or show me just paid invoices. When the user selects that, it automatically updates the records in the form by changing an SQL statement.

That will allow us a lot of flexibility, and all kinds of new options will open up for our form design once we learn how to do this.

This class follows Access 310. I strongly recommend you take at least 301 through 310 before taking this class. If you skipped our 100 or 200 level classes, that is okay if you know Access already, but do not skip up to 310 from 301.

I will be using Access XP in this class. These lessons should be valid for any version of Access from Access 97 all the way up through 2003. I have not tested this material yet with 2007, but I do not think there should be a problem with any of the materials from today's class.

As always, I recommend you sit back, relax, and watch the lesson through one time. Then watch it again, doing the examples along with me the second time.

All the sample database files can be found here at accesslearningzone.com/students.

Of course, feel free to discuss this class online. Accesslearningzone.com?GOAC311 is the direct link to this class, and the class discussion web will open up automatically in your Amicron video player if you are online while you are watching this class.

Quiz

Q1. What is the first main topic covered in this Access 311 class?
A. Adding unit cost to orders
B. Creating customer lists
C. Importing data from Excel
D. Designing new databases

Q2. Why is adding a unit cost to products important?
A. To generate net profit calculations
B. To increase the price automatically
C. To calculate sales tax
D. To sort products alphabetically

Q3. What does the sales report discussed in this course do?
A. Shows all sales broken down by month with totals
B. Summarizes all product details
C. Lists suppliers and inventory levels
D. Shows customer phone numbers only

Q4. What feature will allow users to specify a date range for sales reports?
A. A form with range selection
B. A search query in tables
C. A report wizard only
D. A sorting macro

Q5. What is a dynamic SQL form used for in this class?
A. To change form records based on user-selected criteria
B. To send email notifications
C. To back up the database automatically
D. To enforce password protection

Q6. How does the dynamic SQL form update records in the form?
A. By changing an SQL statement when criteria are selected in a combo box
B. By running a VBA macro at startup
C. By importing an external file
D. By refreshing the entire database

Q7. What does Richard recommend before taking this course?
A. Completing classes 301 through 310
B. Starting directly from this course
C. Taking a Word or Excel class first
D. Skipping all previous Access classes

Q8. Which versions of Access are these lessons valid for?
A. Access 97 through 2003
B. Only Access 2007 or later
C. DOS versions of Access
D. Access 2019 exclusively

Q9. What approach does Richard recommend for watching the lessons?
A. Watch once all the way through, then again doing the examples
B. Fast forward to the end
C. Only read the provided documentation
D. Watch with the sound off

Q10. Where can sample database files for this class be found?
A. At accesslearningzone.com students
B. On a popular social media site
C. Only in the printed textbook
D. At your local library

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A

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 some important new features for your Microsoft Access database. In this lesson, I begin by showing you how to add unit costs to your order records. This involves assigning a cost value to each product, which then allows you to track expenses for every sale. By doing this, we gain the ability to calculate net profit on each transaction, giving you deeper insight into your business's financial performance.

Next, I walk you through building a monthly sales report. This report is designed to summarize all of your sales data, organized and totaled by month. To complement the report, I also create a form where you can enter a date range. This allows you to filter the sales, for example, to see just the transactions between January and March of 2007, or any other period you choose.

After that, I introduce a very useful tool called a dynamic SQL form. With this technique, we can make a form that updates its contents based on criteria selected by the user. For instance, you might want to view all invoices, only quotes, or just the invoices that have been paid. The user can make a selection in a combo box, which then automatically updates the form's records by modifying the underlying SQL statement. This approach not only offers a great deal of flexibility, but also opens the door to many creative options for future form designs.

This class builds directly on the material covered in Access 310. I highly recommend that you complete courses 301 through 310 prior to starting here, unless you already have experience with Access at an advanced level. It's not advisable to jump from 301 right into 311, especially if you've missed the foundational concepts covered in the earlier classes.

For this class, I am working with Access XP, but everything we cover should work just fine with any version from Access 97 up through Access 2003. While I have not tested these tutorials on Access 2007 yet, I do not anticipate any significant issues with the concepts taught here.

My suggestion for making the most of this lesson is to first watch the entire video so you can get an overview of the process. Then, view it a second time and work through the examples alongside me. All of the sample databases you need can be downloaded at accesslearningzone.com.

You are welcome to discuss anything from this class with other students online. You will find a direct link to this course at accesslearningzone.com and the class discussion forums are available for you.

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

Adding unit cost to order details
Tracking product cost for sales
Calculating net profit on sales
Creating a monthly sales report
Designing a sales report form with date range
Filtering sales by custom date range on forms
Building a dynamic SQL form in Access
Using combo boxes to change form record sources
Dynamically updating forms with SQL statements