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

|