Free Lessons Courses Seminars TechHelp Fast Tips Templates Topic Index Forum ABCD

 Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

 < 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: \$23.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/17/2022 Append Mistake #2 8/15/2022 Append Mistake #1 8/12/2022 Transpose Data 8/11/2022 Crosstab 8/9/2022 Append Query 8/8/2022 Indexing 8/5/2022 No Data 8/4/2022 Secondary Emails 8/1/2022 Limit Long Text 7/29/2022 Validation Rules

 Learn  Access - index Excel - index Word - index Windows - index PowerPoint - index Photoshop - index Visual Basic - index ASP - index Seminars More... Customers  Login My Account My Courses Lost Password Memberships Student Databases Change Email Info  Latest News New Releases User Forums Topic Glossary Tips & Tricks Search The Site Code Vault Collapse Menus Help  Customer Support Web Site Tour FAQs TechHelp Consulting Services About  Background Testimonials Jobs Affiliate Program Richard Rost Free Lessons Mailing List Order  Video Tutorials Handbooks Memberships Learning Connection Idiot's Guide to Excel Volume Discounts Payment Info Shipping Terms of Sale Contact  Contact Info Support Policy Email Richard Mailing Address Phone Number Fax Number Course Survey