Create a multistep macro

- [Instructor] Macro objects allow you to create a series of actions you'd like to automate. With a macros trigger, Access runs through the steps and performs the tasks for you. There's lots of different uses for macros in your database and the benefit of using them is that every time that they're triggered, the macro will perform the exact same steps, in the exact same order, regardless of what user initiated the process. This makes them invaluable in ensuring that different users will get the same results, especially on more complex, multi-step processes. Let's suppose that I want to start each day with two very specific pieces of information from the database opened up for me to review. I like to see the most recent orders from the orders table. If I open it up, we'll see that I have a total of 3,472 orders. I'd just like to see the orders from the last 180 days, and have them presented in a reverse chronological order, so that the most recent are at the top. I'd also like to see the customer lifetime sales report. If I open that up, we'll see that it totals every customer in every state. Let's suppose that I just want to see the people that have spent over a thousand dollars across all of their orders. In order to get both of these objects set up, it's going to take a number of clicks and filters. Conveniently, this is a perfect use for a macro instead. Let's go ahead and close out of this report, and I'll close the table as well. Then we'll go to the create tab, and I'll start creating a new macro. If your action catalog isn't opened up on the screen, you can toggle it on by pressing the action catalog button here on the ribbon. Then I'm going to go into the database objects folder, and double-click on "open table". That'll add in the first step of my macro. The table name that I want to open is the orders table, I can go ahead and either type it in or select it from the dropdown menu. And I want to open it in the data mode called "read only". I want to make sure that I'm not accidentally making changes to the table while I'm reviewing it. Next I want to filter down the results in this table. We can do that by going back to the action catalog and I'll collapse the database objects folder, and open up the folder called "filtered query search". Then I'll add in the action called "apply filter" by double-clicking on it. And then I'll add in step number two. We're going to come down to the "where condition" argument, and this will the same condition that you'd put into a queries criteria row to filter the record, or in an SQL statement's "where" clause. I only want to see the orders that are within the past 180 days, so I can make reference to the "order date" column by typing that in square brackets and I want to say "greater than" a date that's 180 days in the past. I can do that by calculating today's date by using the date function, I'll type in an open parenthesis to enclose this whole statement, the date function doesn't have any arguments of its own, so I'll just open and close parentheses there, then I'll subtract 180 from that, and I'll just end that with a closing parenthesis. So essentially what we're doing is getting today's date with this function, and we're subtracting 180 days from that. That'll find a date that 180 days in the past, then I want to filter my table to just those orders that have an order date that's after that day in the past. So that finishes the filter. Let's go down to the next step. I also wanted to sort my records. I can come back to the filter query search folder and double-click "set order by" to add in that action. Then we're going to order by the order date column. Now if I just left it like this, it's going to sort it ascending, so I need to specify D-E-S-C at the end, to sort that column descending. Then we'll finish the table setup, let's go ahead and add in the next action, we'll come back to the database objects folder, and this time we're going to "open report". The report that I want to open is the customer lifetime sales report. I want to view it in "print preview" view and we also want to apply a "where" condition to the report. I only want to see the customers that had a lifetime sales above one thousand dollars. So I'll make a square bracket here, and we'll type any reference to the lifetime sales column, and I'll simply say "greater than 1000". I'll click down at the bottom to de-select everything and we'll take a look at the finished macro. If I want to collapse the steps I can do that by coming up here to the ribbon and pressing the "collapse actions" button. That'll reduce everything to a single line, so that I can see the action name, and the arguments in parentheses. I can also press the "expand actions" button to re-expand them open again. Let's go ahead and save this macro, I'll call it "morning review". And then I'll save it down here in the navigation pane. Now we can go ahead and close it down, and we'll test it out by double-clicking on it. That'll open up both the orders table, and the customer lifetime sales report. If I take a look at the report, we'll see just the customers that have spent over $1000 with the company, and if I take a look at the orders table, we'll see that it's returning just the most recent order dates, and it's sorted reverse-chronologically. Now keep in mind that the results that I'm getting here on the orders table is based off of the day that I'm recording this movie. Remember that we're filtering the records to just the ones that are within 180 days of the day that this macro is being run. It's likely that you'll have fewer records returned or none at all, and you can simply adjust the date range in the macro's design as needed. And with that we have my completed morning review macro. It goes through a number of steps to quickly bring up the exact information that I'd like to review when I sit down at my desk at the start of each day.