Access Expert Level 17

This class picks up where Expert Level 16 left off. We are continuing our work with Action Queries and UNION Queries. We will also learn about Crosstab Queries, which are just like PivotTables in Excel. We will be pulling together all of the skills we've developed with Action Queries to create a system to archive old, unpaid orders. We'll move them from the order table to an order archive table.

Today we will begin by creating a macro that will automatically mark orders that are unpaid and older than 1 year as "To Be Archived."

As a quick tangent, I will teach you about the differences between embedded button macros and database system-level macros. We'll use this to create a button on our Quick Access Toolbar that we can use to open up / jump to our Main Menu any time we want. It's a pain to always have to find the Main Menu in the navigation pane in order to open it - either if you've closed it, or if you have 10 windows open on top of it.

Back to business, we will customize the Order List form we created in a previous class to show just the orders that are marked "To Be Archived." This way we can quickly and easily review the list and uncheck any order we don't want being archived.

We will then create two tables to hold our archived records: one for the order information, and another for the order details. We'll learn about the "duplicate output destination" error that sometimes comes up with append queries, and how to deal with it.

Next we will create queries to make backups of our Customers, Orders, and Order Details. It's a good idea to run these BEFORE running queries that will change your tables. We'll include these in the macro.

We want our sales reps to know if they pull up a customer's record and he has any previous orders that were written off (unpaid). So we'll create more queries to add a "Write-Off" notice to the customer's notes and we'll add this as an event in his contact history as well. This will involve an update query (to append text to a single field) and an actual append query.

Now that we've backed everything up, given the user the opportunity to review the changes, and appended the records to the archive table, we're ready to actually delete the records from the order and order details table. We'll review the delete query FROM and WHERE clauses, create a button on the order list form to launch the macro, and we'll learn about the new CloseWindow command to close the form after the macro is finished.

There may come a time when you want to see ALL of the customer's orders - both the current ones and the archived "write off" orders. In that case we'll need a UNION query to bring them all back together again into one recordset.

Now that we're all finished with the Archive Old Unpaid Orders project, we'll start on a new topic: Crosstab queries. These are very much like PivotTables in Microsoft Excel. They allow you to take a whole bunch of data and summarize it based on one or more fields. For example, you can say, "show me all of my sales totals broken up by date on the vertical axis and state on the horizontal axis," and you'll end up with something like this:

This is the 17th class in the Access Expert series. There's a lot of great material in this class. Learning Action, Union, and Crosstab Queries will add tremendous power to your databases. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

Complete Outline - Access Expert Level 17