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