Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  

< Previous: Access Expert 7

Next: Access Expert 9 >

Access Expert Level 8

Expert Microsoft Access Tutorial - 1 Hour, 55 Minutes
This Microsoft Access video tutorial picks up where Expert Level 7 left off. In this class we will begin building our Order Entry System. We'll start out by learning how to create Calculated Query Fields. We'll calculate an extended price and sales tax for our order items. We'll learn how to properly Round values to avoid fractional penny errors. We'll build an Order Form and Order Details Form (for line items), and lots more. Topics include:
  - Order Entry System
  - Order Form and Details Subform
  - Calculated Query Fields
  - Figuring Sales Tax if Taxable
  - IIF Function (If/Then/Else)
  - Proper Rounding of Values
  - Bankers Rounding
  - Nesting Functions
Final Product and Tax Totals

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.


Access Expert Level 8
Description: Access Expert Level 8
Versions: This class was recorded with Microsoft Access 2013. The material is valid for Access 2007 up to 2021. Access has not changed that much over the years.
Pre-Requisites: Access Expert Level 7 strongly recommended
Running Time: 1 Hour, 55 Minutes
Cost: $23.99

This class picks up where Expert Level 7 left off. We will start by reviewing my solution to the homework assignment you were given in the last class. You were to design a form with a many-to-many relationship showing all of the products sold by a particular vendor (the reverse of the form we designed in the last class).


Next, you will design an Order Details table to store information on all of the line items on an order. This will allow you to have an unlimited number of items on each order. We'll create a query to pull in the related details from the products table (price, name, etc.)


Next we will learn about Calculated Query Fields. You'll learn how to multiple the unit price and the quantity purchased to determine the total amount to charge for each line item. We'll review all of the math operators, learn about integer division, modulus, the order of operations, logical constants, boolean values, and lots more.


Once we know how to calculate values in queries, we can determine the amount of sales tax that needs to be paid. However, we also need to take into consideration whether or not each item is taxable (computer parts are, a gallon of milk is not). So we'll learn about the IIF Function, which is a way to have Access make IF-THEN-ELSE decisions inside a query. We'll also learn about the ROUND function, bankers rounding, nesting functions, and more.


Now we can create our Order Details form to allow the user to enter in products and begin to build an order. We'll create a combo box so the user can pick a product. The unit price will be automatically displayed (from the product table). We'll set up relationships between the customer and order tables (so orders will NOT be deleted if a customer is) and between the order and order details table (so the line items ARE deleted if an order is). This is a good example for referential integrity and cascade deletes that we learned in earlier classes.


Finally, we'll put everything together into an Order Form. We'll design the form, add a combo box to pick a customer, and make it so that our combo box will display the customer's company name if it exists, otherwise display the contact's first and last names (another example of the IIF function). We'll add a button so we can get to the order form FROM the customer form. We'll calculate totals for everything on the bottom of the form (product total, sales tax total, and a grand order total).


This is the eighth class in the Access Expert series. If you are interested in learning how to build an order-entry system, then this is the class where we start developing its foundation. Of course, if you have any questions about whether or not this class is for you, please contact me.


Complete Outline - Access Expert Level 8

00. Intro (8:22)

01. Products to Vendors (24:01)
Homework Review
Button to Open Product Form
VendorF Form
Button to Open Vendor from Product
Button to Open Product from Vendor
Give Names to VendorCombo, ProductCombo
Query to add UnitPrice to Junction
Add Price and Profit to Vendor SubF

02. Order Details Table (11:42)
Create OrderDetailT
Delete AmountDue from OrderT
Possibility of breaking stuff!
Create OrderDetailQ
Pull in data from ProductT

03. Calculated Query Fields (12:29)
Calculate ExtPrice
String Concatenation Reviewed
Assignment Operators
* / + - \ ^ MOD
Integer Division
Order of Operations
Comparison Operators
< > <= >= <> =
-1 or 0 for True False
Boolean Values
Format Property in Query Column
Format True/False
Format Currency
04. Sales Tax (16:32)
IF THEN Statements
IIF Function
Function Review
SalesTax IIF Function
ROUND Function
Rounding Numbers
Bankers Rounding
Traditional v Bankers Rounding
Nested Functions
Nesting Functions
Fractional Penny Problem
INT Function Round Down

05. Order Detail Subform (14:44)
Create OrderDetailF Subform
Product Combo Box
Lock UnitPrice Field
Order Relationships
Customers to Orders
Do NOT Cascade Delete
Ordsrs to Details
DO Cascade Delete

06. Order Form (22:18)
Create OrderF
Customer Combo Box
Show Name if Company Null
Is Null vs IsNull()
Add OrderDetailSubF
Auto Default CustomerID
Auto Default IsTaxable
Form Footer Calculations
Sum of ExtPrice
Sum of SalesTax
Grand Total

07. Review (5:06)


Keywords: Order Entry Form, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, vendors, products, order details, line items, integer division, modulus, if/then statement, IIF function, calculate sales tax, rounding, round function, bankers rounding, nested functions, int function, order form, order details form, totals


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 6/17/2024 4:41:23 PM. PLT: 0s