Access Expert Level 8

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