Access 2007-2019 Access 2000-2003 Access Seminars TechHelp Support Tips & Tricks Access Forum Course Index Topic Glossary Insider Circle

 Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us More... What's New? Tips & Tricks Access Index Access Tips Excel Index Excel Tips Waiting List

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

Access Expert Level 8
 Description: Access Expert Level 8 Versions: Recorded with Access 2013. Also use with 2007 and 2010. Pre-Requisites: Access Expert Level 7 strongly recommended Running Time: 1 Hour, 55 Minutes Cost: \$24.99

This class picks up where 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 VendorProductSubF Button to Open Vendor from Product Button to Open Product from Vendor Give Names to VendorCombo, ProductCombo Query to add UnitPrice to Junction VendorXProductWithProfitQ 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 Modulus Order of Operations PEMDAS Comparison Operators < > <= >= <> = AND OR NOT XOR -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 SUM AVG MAX MIN COUNT DATE NOW TRIM 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

 You may want to read these articles from the 599CD News:
 8/15/2019 Last Chance for Access CDs 8/1/2019 Customer List Form Template 8/1/2019 New Template Access Customer Database 7/30/2019 Microsoft Access Developer 13 6/20/2019 TIP: Validation Rules in Access 11/20/2018 Microsoft Access Developer 12 8/25/2018 NEW: Access Dev 9, 10, 11 8/25/2018 Microsoft Access Developer 11 8/25/2018 Microsoft Access Developer 10 8/25/2018 Microsoft Access Developer 9

Learn

 Access index Excel index Word index Windows index PowerPoint index Photoshop index Visual Basic index ASP index Seminars More...
Customers

Online Theater
Insider Circle
Student Databases
Change Email
Info

Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Help

Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services

Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order

Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact

Live Chat
General Info
Support Policy
Contact Form
Email Us