Introduction

Welcome! Archive Unpaid Orders & Crosstabs

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Expert Level 17. In this course we will work on a project covering action queries by archiving old unpaid orders in our database. We will walk through creating update, append, make-table, and delete queries, backing up necessary tables, and marking archived orders with write-off notices. We will use union queries to combine current and archived orders for reporting purposes and discuss how to work with button macros to automate these actions. Finally, we will introduce crosstab queries, which summarize and analyze data similarly to pivot tables in Excel.

Navigation

Prev: X17
Next: Lesson 01

Keywords

Access Expert, action queries, archive old orders, update query, append query, delete query, make-table query, order backup, archive table, order details, write-off notice, union query, macro commands, OpenQuery, SetWarnings, crosstab query, pivot table

Subscribe to Introduction
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Expert, level 17, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to do a project related to action queries. We are going to take all the things we have learned in our classes so far about action queries and put them all together in one project.

We are going to be archiving old orders in our database. We are going to take orders that are unpaid, that are more than a year old, and move them to an archive table. We do not want them cluttering up our main order table, but we do not want to lose them completely. So we are just going to move them into a different table.

We will begin with creating an update query that will mark all of the orders that the database thinks should be archived. We will create an order list form that will allow us to review all of the orders that are marked to be archived, so we can go down and check off the ones that we do or do not want to archive from that point. That way the database does most of the work for us, but we can still choose to archive or not.

We will make an archive table, and an order backup table. We will back up the order table first using a make-table query. Then we will append the orders that are going to get archived into the archive table, same with the order details. We will also back up the customer table too just to be safe. Then we will delete the orders out of the primary order table since we no longer need them.

All of this, of course, when we are done, will happen with one button click. Then we will add write-off notices to the database so that the customer note field says that there is an order write-off. We will also put an order write-off notice in the contact records. We will append a contact. That way, if any of the sales reps look at this customer record, they will see "hey this guy's got a write-off from December 6th" or something.

Then we will make a union query to put them back together again temporarily. If you want to put the orders and the archived orders back together in one query, perhaps you want to make a report showing all of the customer's orders, both the current ones and the ones that have been written off. That is what a union query is good for.

And of course we will learn how to do all of this by editing a button macro using some macro commands like OpenQuery and SetWarnings and CloseWindow. We will learn some new macro commands today. Again, the focus of the advanced series will be on editing and designing your own macros. But for today, I just want to teach you some basics for getting in there and working with some of the button macros that Access creates. By just tweaking those, we can make our databases much more powerful.

And then we will begin a brand new concept today called a crosstab query. Now, if you are familiar with Microsoft Excel, crosstab queries are just like pivot tables in Excel. You have big lists of data with multiple different types of fields in them, and you can set up a row field and a column field and then what data you want to summarize in the center.

For example, you can take a big long list of sales and say, "Break this down by date and by state, and show me the sum for each date for each state." That is what the screenshot shows there. Crosstab queries are very powerful and we will see how to use those today.

This class follows Access Expert levels 13 through 16. I strongly recommend you watch all of those classes before today's class. Most of today's class is a project that summarizes a lot of the information we have learned in those previous classes. If you do not take the time to learn about update and append and delete queries and so on, you will probably be lost in today's class.

We will learn a bunch of new techniques for dealing with these older topics in today's class. I am going to show you a bunch of new tricks and tips. But I strongly recommend you take these background classes before today's class.

Today's class was recorded with Access 2013. You should be okay if you are using 2007 or 2010. If you are using Access 2003 or earlier, you want to go to my website and get Access 220 through 223. Those are the courses in which I cover action queries and crosstab queries.

My courses are broken up into beginner, expert, advanced, and developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the expert level classes, which you are in now. When you finish all the expert level classes, the advanced classes will cover event programming and macros, and the developer classes will cover Visual Basic for Applications. Each group of classes is broken down into multiple levels, level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more. You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked, as well as my responses to them, and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson, and feel free to join in the discussion. If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum and I will do my best to help you.

Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Now, I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database.

One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes up in Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now, let's take a few minutes and go over exactly what we are going to cover in today's class.

In lesson one, we are going to begin by creating a system to archive old unpaid orders from our order table.

In lesson two, we are continuing with the archiving of old unpaid orders.

In lesson three, we are continuing to archive old unpaid orders. In this lesson, we are going to use append and update queries to put an order write-off notice in the customer record, the customer's contacts, and write on the order itself.

In lesson four, we are going to finish up with the archiving of old unpaid orders. We are going to run the delete queries to actually remove those orders from our primary order tables. We are also going to make a union query so we can see them together again later if we want to.

In lesson five, we are going to learn about crosstab queries. A crosstab query is very similar to a pivot table in Microsoft Excel. We will create a crosstab query to show sales by state, broken up by date.

Intro

Quiz

Q1. What is the primary purpose of the project in this class?
A. To archive old, unpaid orders to prevent clutter in the main order table
B. To permanently delete all unpaid orders
C. To combine archived and current orders into one table
D. To back up customer email addresses

Q2. Which type of query will FIRST be used to mark orders for archiving?
A. Update Query
B. Append Query
C. Delete Query
D. Select Query

Q3. What feature is added to allow users to review which orders are marked to be archived?
A. Order List Form
B. Crosstab Query
C. Union Query
D. Macro

Q4. Before deleting records from the order table, what step is recommended?
A. Back up the table using a Make-Table query
B. Empty the archive table
C. Erase all customer data
D. Print all records

Q5. Why would you use an Append Query in the archiving process?
A. To move marked orders into the archive table
B. To delete orders from the main table
C. To mark orders to be archived
D. To create indexes on the archive table

Q6. What is the purpose of a Union Query in this project?
A. To display current and archived orders together in one query
B. To merge customer contact information
C. To summarize sales data by state
D. To update customer write-off notices

Q7. Which macro commands will you be introduced to in this class?
A. OpenQuery, SetWarnings, CloseWindow
B. RunSQL, EditRecord, PrintPreview
C. SaveAs, ExportData, ImportData
D. SetFocus, GoToRecord, FilterByForm

Q8. What is a Crosstab Query most similar to in Microsoft Excel?
A. Pivot Table
B. Chart Wizard
C. Slicer
D. Data Validation

Q9. When should you attempt to apply what you learn in this lesson to your own projects?
A. After building and mastering the sample database from class
B. While watching the video the first time
C. Before watching the class videos
D. Only after finishing all Developer-level courses

Q10. What additional step is taken to notify sales reps of a write-off related to a customer?
A. Append a write-off notice to the contact records
B. Delete the customer's contact records
C. Mark all customer orders as paid
D. Send emails to the sales reps

Q11. Where can you find downloadable copies of the sample databases built in the course?
A. accesslearningzone.com/databases
B. Microsoft Office website
C. Northwind Traders help page
D. The Access splash screen

Q12. If you are using Access 2003 or earlier, which courses should you refer to for action queries and crosstab queries?
A. Access 220 through 223
B. Access 130 through 133
C. Access 113 through 116
D. Access 310 through 313

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on how to use action queries in Microsoft Access by putting together everything we have covered in previous classes into a single, comprehensive project. The focus of this lesson is developing a system to archive old, unpaid orders in your database. The goal is to remove unnecessary clutter from your main order table while still maintaining access to older records by moving them into an archive table.

We will start by creating an update query that identifies and marks orders that should be archived - specifically, those that are unpaid and older than a year. We'll then design an order list form that allows you to review and manually select which of the flagged orders will actually be archived. This hybrid approach lets the database do most of the heavy lifting while still giving you final control over what's archived.

Next, we'll set up both an archive table and an order backup table. We'll back up the order table first with a make-table query to ensure the data is preserved. Using append queries, we'll move the selected orders and their related details into the archive table. As an added precaution, we will also back up the customer table. Once that's completed, the archived orders will be deleted from the primary order table.

The entire archive process will eventually be triggered with just a single button click. To round out the workflow, we'll add write-off notices in the database: the customer note field will reflect the order write-off, and we'll attach a write-off notice to the contact records. This provides visibility to sales reps, letting them know if a customer has had orders written off and when.

Additionally, we'll create a union query that can merge archived and active orders into a single view. This is especially helpful if you need to generate reports that cover both current and archived data for a customer.

Along the way, I will introduce you to working with button macros in Access using commands like OpenQuery, SetWarnings, and CloseWindow. Today's lesson will show you how to tweak button macros that Access generates, helping you take greater control over your database actions.

We will also introduce a new concept today called crosstab queries. These are similar to pivot tables in Excel. They allow you to summarize large sets of data by different row and column fields, making it easy to break down something like sales by date and by state, and see totals at each intersection point. You will see how powerful and versatile crosstab queries can be.

Today's class builds on what we covered in Access Expert levels 13 through 16. If you haven't yet studied those lessons, I highly recommend you go through them first. Much of this project requires a good understanding of update, append, and delete queries.

Even if you're already familiar with the basics, I'll be sharing new techniques and tips in this class that will deepen your understanding. This course was filmed using Access 2013, but if you are using Access 2007 or 2010, the instructions should still apply. If you're working with Access 2003 or earlier, you will want to look up my Access 220 through 223 courses for information on action and crosstab queries.

My series of classes is arranged from beginner through developer levels. If you've reached these expert classes, you should be comfortable with all of the material from the beginner series already. The advanced series will take you into event programming and more complex macros, while the developer classes focus on Visual Basic for Applications.

Beyond the standard courses, I offer special seminars on topics like building web-based databases, designing calendar-style forms and reports, improving security, handling images and attachments, managing work orders, tracking accounts payable, learning SQL, and more. You can find a list of these seminars on my website.

If you have any questions about the class topics, I encourage you to participate in the student forums. If you are watching this lesson on my website, you'll find the forum right next to the video, where you can see questions from other students and my responses. Engaging in these discussions can really enhance your learning. You can also access the forums directly at accesslearningzone.com/forums if needed.

My advice for getting the most from this class is to watch each lesson from start to finish first without trying to follow along. Once you have watched the example, go back and build the same database step by step as shown. Resist the urge to immediately apply the lesson to your own projects until you have thoroughly understood the given examples. If you run into problems or confusion, re-watch the video or post your question in the forum.

Keep in mind, learning Access can seem challenging at first, but as you gain confidence, you'll find it an easy and powerful tool.

While I encourage you to create the database alongside me, you can also download my completed database file from my site at accesslearningzone.com/databases. Sometimes examining a finished project is the best way to understand how everything fits together.

Let me outline what we'll cover in each lesson today:

- Lesson one introduces the archiving process for old unpaid orders.
- Lesson two continues with the archiving system, refining and working through the steps.
- Lesson three focuses on using append and update queries to place order write-off notices in the customer record, the contacts, and the order itself.
- Lesson four wraps up by using delete queries to remove the archived orders from the main tables and building a union query to view archived and active orders together if needed.
- Lesson five teaches you about crosstab queries, showing sales summarized by state and by date, much like a pivot table in Excel.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Archiving old unpaid orders from the order table
Creating an update query to mark orders for archiving
Designing a review form for orders marked for archive
Making an archive table for old orders
Creating a backup table using a make-table query
Appending orders to the archive table
Archiving related order details
Backing up the customer table
Deleting archived orders from the main order table
Updating customer records with write-off notices
Appending write-off notices to contact records
Combining orders and archived orders with a union query
Editing and designing button macros
Using macro commands like OpenQuery, SetWarnings, CloseWindow
Introduction to crosstab queries
Creating crosstab queries to summarize sales by state and date

Article

In this tutorial, we are going to tackle a practical project in Microsoft Access: archiving old unpaid orders in a way that keeps your main order table clean while still preserving order history. This project will walk you through the process of identifying, reviewing, backing up, and moving these orders to an archive table, along with updating customer records to keep full visibility of any write-offs. We will also introduce crosstab queries, which are a powerful way to analyze data much like pivot tables in Excel.

Let us start with the problem. Suppose you have an orders table in your database, and over the years, it has filled up with orders that are both unpaid and over a year old. You do not want these cluttering up your primary workflow, but you also do not want to lose the order information altogether. The solution is to move them to an archive table while maintaining the ability to refer to them when needed.

The first step is to identify which orders to archive. An effective way to do this is by creating an update query. This query can set a field, perhaps called ArchiveReady, to True for any order that meets your criteria, such as unpaid orders older than a year. You can write the criteria like this in your query design:

IsPaid = False AND OrderDate < DateAdd("yyyy", -1, Date())

This expression finds all orders where payment has not been received and the order date is more than a year ago from today.

Once you have these orders marked, you will want a way to review them before archiving. You can create a form in Access that lists all orders where ArchiveReady is True. This lets you scroll through and check off any you do not want to archive, ensuring you have the final say.

Before making any changes to your data, it is a good practice to back up your tables. You can make a make-table query to copy your Orders table and your customers table to backup tables, such as Orders_Backup and Customers_Backup. The SQL for a basic make-table query looks like this:

SELECT * INTO Orders_Backup FROM Orders;

With your data safely backed up, the next step is to move the old orders to an archive table. First, create your archive tables (such as Orders_Archive, OrderDetails_Archive) with the same structure as your original tables. Then, use append queries to add the selected orders and their details to the archive tables:

INSERT INTO Orders_Archive SELECT * FROM Orders WHERE ArchiveReady = True;

Do the same for related tables, for example, OrderDetails, ensuring you only move the details for the archived orders.

After appending the orders and details to the archive tables, you will want to clean up your primary tables. Use delete queries to remove the archived orders and their details from the main tables:

DELETE FROM OrderDetails WHERE OrderID IN (SELECT OrderID FROM Orders WHERE ArchiveReady = True);
DELETE FROM Orders WHERE ArchiveReady = True;

To keep your team informed of the history, it is helpful to note when an order has been written off. You can run an update query to add a message in the customer's notes field, such as "Order write-off on [date]." Similarly, you can append a new contact record to your Contacts table, noting the write-off for sales reps or customer service to see in the future.

Often, you will want to present all orders for a customer, including those moved to the archive. You can create a union query to combine records from both the main Orders table and the Orders_Archive table. Here is a sample SQL statement for a union query:

SELECT OrderID, CustomerID, OrderDate, Total, 'Active' AS Source FROM Orders
UNION ALL
SELECT OrderID, CustomerID, OrderDate, Total, 'Archived' AS Source FROM Orders_Archive;

This gives you a view of all orders, whether they are current or archived, and distinguishes them so you know their status.

To make this process user-friendly, you can automate the steps with a macro and a button on your form. Use macro actions like OpenQuery to run your queries in sequence, SetWarnings to turn off confirmation dialogs, and CloseWindow to tidy up after your macro runs. Here is a basic example of macro actions you might use:

SetWarnings Off
OpenQuery "qryBackupOrders"
OpenQuery "qryAppendOrdersToArchive"
OpenQuery "qryDeleteOldOrders"
SetWarnings On

This sequence will back up, append, and delete orders, all with one button click.

Now, let us discuss crosstab queries. A crosstab query in Access works much like a pivot table in Excel. It allows you to summarize a table of data by showing one set of values as row headings (such as dates), another set as column headings (such as states), and the summarized data (like the sum of sales) in the cells. For example, if you wanted to see total sales by date and by state, your crosstab would set dates as row headings, states as column headings, and sum the sales in the value field. In the query design grid, choose Crosstab as the query type, then specify which fields you want for rows, columns, and values. This is a powerful way to analyze trends or summarize large tables quickly.

To summarize, the process of archiving old unpaid orders in Access involves marking orders for review, creating backup and archive tables, appending and deleting records with queries, updating customer notes, combining active and archived data with union queries, and optionally automating everything with a macro. Along the way, we also introduced crosstab queries as a data analysis tool. With these techniques, your database will be better organized, safer, and more informative for everyone who uses it. Remember to always back up your data before performing bulk operations like append or delete queries, and review your archive selections before finalizing changes. With a bit of setup and understanding of these concepts, managing and analyzing your database becomes not only possible, but efficient and secure.

Primary Topics

action queries, archiving records, update queries, append queries, delete queries, make-table queries, union queries, crosstab queries, macros, database backup

Secondary Topics

macro commands, report generation, database security, forum usage