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
00. Intro (9:05)
01. Archive Old Unpaid Orders 1 (13:30)
Add ToBeArchived y/n Field Create ToBeArchivedUpdateQ
Query Button to Archive Old Orders on Main Menu Database
Level Macro Primer Macro to Open Main Menu Putting Macros
on Quick Access Toolbar
02. Archive Old Unpaid Orders
2 (21:22)
Make OrderListF Show All Order Add ToBeArchived to Order
List Form TBA Tool Tip To Be Archived checkbox on Order
Form Macro WHERE Condition ToBeArchived=TRUE Create
OrderArchiveT Create OrderDetailArchiveT Set Autonumbers
to Long Int Append Orders Create AppendOrderDetailArchiveQ
Create AppendOrderArchiveQ Duplicate output destination
Create OrderToBeArchivedQ to avoid error
03. Archive
Old Unpaid Orders 3 (21:27)
Backup tables with MakeTable query MakeCustomerBackupQ
MakeOrderBackupQ MakeOrderDetailBackupQ Calculated columns
are not allowed in SELECT INTO statements Another reason I
don't like calculated table fields Remove DaysToShip from
OrderT Update customer record "Order Writeoff"
UpdateCustomerOrderWriteoffQ Append "Order Writeoff" to
ContactT Put writeoff notice in contact table Add OrderID
to writeoff notice AppendOrderWriteoffQ Update the order
description with "WRITEOFF!" UpdateOrderWriteoffQ |
04. Archive Old Unpaid Orders 4 (13:02)
Delete orders and details from main tables
DeleteArchivedOrderDetailQ DeleteArchivedOrderQ Copy and
modify append queries Delete FROM and WHERE Make button on
TBA form to perform archive Copy button with macro in it
CloseWindow macro command Macro commmand to Close Form
UNION query to show orders and archive together SQL View
Fields must match exact with UNION and * all fields
05. Crosstab Queries (13:16)
What is a Crosstab Query? Similar to an Excel PivotTable
Show example of a PivotTable Add some new orders Edit
OrderListQ Add State Field Create Crosstab Query Crosstab
Query Wizard Built Crosstab Query Manually Set up Helper
Query Orders by Month by State
OrderByMonthByStateCrosstabQ
06. Review (5:38) |

|