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
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
00. Intro (9:05)
01. Archive Old Unpaid Orders 1 (13:30)
Add ToBeArchived y/n Field
Button to Archive Old Orders on Main Menu
Level Macro Primer
Macro to Open Main Menu
on Quick Access Toolbar
02. Archive Old Unpaid Orders
Make OrderListF Show All Order
Add ToBeArchived to Order
TBA Tool Tip
To Be Archived checkbox on Order
Macro WHERE Condition
to Long Int
Duplicate output destination
Create OrderToBeArchivedQ to avoid error
Old Unpaid Orders 3 (21:27)
Backup tables with MakeTable query
are not allowed in SELECT INTO statements
Another reason I
don't like calculated table fields
Remove DaysToShip from
Update customer record "Order Writeoff"
Append "Order Writeoff" to
Put writeoff notice in contact table
to writeoff notice
Update the order
description with "WRITEOFF!"
04. Archive Old Unpaid Orders 4 (13:02)
Delete orders and details from main tables
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
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
OrderListQ Add State Field
Create Crosstab Query
Built Crosstab Query Manually
Set up Helper
Orders by Month by State
06. Review (5:38)