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  
 
 
 

< Previous: Access Expert 8

Next: Access Expert 10 >

Access Expert Level 9

Expert Microsoft Access Tutorial - 1 Hour, 45 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 8 left off. In this class we will continue to learn how multiple forms can link together to share data. We will work with customer and order discounts, sales tax rates, and creating a printable invoice report. Topics include:
 
  - Sharing Data Between Forms
  - Customer & Order Discount Rates
  - Default System Values Table
  - Multiple Sales Tax Calculations
  - SQL ALIAS
  - Printable Invoice Report
  - Inserting Subreports
  - Problems with Can Shrink
  - Open Report to a Specific Record

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 9
Description: Access Expert Level 9
Versions: Recorded with Access 2013. Also use with 2007 and 2010.
Pre-Requisites: Access Expert Level 8 strongly recommended
Running Time: 1 Hour, 45 Minutes
Cost: $24.99


This class picks up where Expert Level 8 left off. We will start by reviewing my solution to the homework assignment you were given in the last class. You were to use the discount rate on the customer form to set a discount rate on the order form, which in turn sets the discount rate for each line item on the order. This is an exercise in making multiple forms work together to share data.

 

Next, you will learn how to set up a similar relationship to calculate sales tax. Each customer can have their own sales tax rate (some are tax exempt, others out of the state, etc.). Each order can have its own sales tax rate based on the customer. This will set the sales tax for the line items. We'll also set up a system default values table so we can set the default sales tax rate for new customers.

 

To add another level of complexity, some products are taxable (computers) while others are not (a gallon of milk). We need to make our database smart enough to realize when to not calculate sales tax based on the product selected. You'll also learn about SQL ALIASes, and how to use the IIF function to display a "T" for taxable in the product combo box.

Now, keep in mind that even if you're NOT ever going to work with sales tax in your database, these techniques are useful for many different types of situations. This is just the example that I've chosen. You should learn this stuff!

 

Next we'll spend a good deal of time putting together a printable invoice report. You'll learn how to insert a subreport, work with report headers/footers and page headers/footers, create an order query with additional customer data, and more.

 

Continuning with our invoice report, we'll add the company info to the header. If we're missing a company name, for example, we want to use the Can Shrink property to get rid of that empty space - but there are some problems we have to learn how to address first. We'll learn how to deal with adjacent and overlapping controls in our reports. You'll see how to use a query criteria to open a report to a specific record so you can print just one specific invoice.

 

This is the ninth class in the Access Expert series. This class is crucial if you want to build an order entry system and calculate sales tax, but even if you think you're NEVER going to need to work with this stuff, the concepts taught in this class will help you regardless of the type of database you're building. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 9

00. Intro (8:39)

01. Customer Discount Rate (20:26)
Discount Rate for Orders
Discount Rate for Order Details
Different Validation Rules
Recalculate Sales Tax
Tab Cycle Property
LineTotal

02. Multiple Sales Tax Rates (17:42)
SalesTaxRate for Customers
SalesTaxRate for Orders
SalesTaxRate for Order Details
Values Propagate Down Forms
Disable Close Button
Disable Control Box
System Default Values Table

03. Non Taxable Products (12:01)
IsTaxable for Products
Show IsTaxable in Product Combo
SQL ALIAS
IF Product Tax Exempt
Another IIF Example
04. Printable Invoice 1 (20:27)
Order Detail Subreport
Report Header / Footer
Page Header / Footer
Order Info with Customer Data
OrderQ
Customer Bill To Info
Insert Subreport

05. Printable Invoice 2 (19:58)
Company Info in Header
Missing Company Name Gap
Can Shrink Problem
Adjacent Control Problem
Overlapping Controls
TRIM Function
Open Report to Specific Record
Query Criteria
Company Return Address Info

06. Review (6:08)

 


 
Keywords: Printable Invoice, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, discounts, discount rate, validation rules, sales tax, line total, disable close button, disable control box, SQL ALIAS, IIF Function, order details, page header, page footer, report header, report footer, subreport, can shrink, trim function, query criteria
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
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
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
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
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP