The Access Report Wizard can be used to create a basic report by gathering relevant records from related data tables, and formatting them based on a couple of preferences. The finished report will likely require some additional design finesse to be fully usable, but the Wizard provides a quick start.
- [Instructor] In the check-in details report that we just created, Access placed a single line for each record that was coming out of the original query. It's currently sorted chronologically based off of the check-in date which is a logical way to view this information, but it's not the only way. What if I wanted to see all of the details for each guest? For instance, I wanted to see Anna Cunningham, followed by her entire check-in history, and then Carl Garza, followed by all of his check-in history. We can get more control over the layout of our report by allowing the report wizard to walk us through the process of collecting and organizing our data.
Let's create another report based off of the check-in details query, but this time, we're going to make use of the wizard. First, I'll go ahead and close out of this report and then I'll come over here to the Create tab, and in the report's group, click on Report Wizard. Now we've seen this interface several times now. The first step of creating a report is to build a query to gather the records that we want to display, and that's exactly what's happening here. We're building that first query, but this time, instead of pulling information straight out of a table, we're actually gonna pull the data out of another query, that check-in details query. So make sure that that's selected in this dropdown menu, and then we're gonna use all of the available fields, and move them over to selected fields.
However, I want to make sure that the last name field appears first in the selected field section, so go ahead and select that one and move it over, and then go ahead, and move over all the others. When that's done, go ahead and press the Next button. The report wizard then asks us how we want to view our data. We can either organize it by guest, which will group all of the information from the guest table, including the last name, first name, and phone number together for each of our guests, and then below that, we're gonna see the check-in details, the date, rate code, and room ID. This is the organization that I want, but the other option, by room assignments, is gonna group everything together into a single row, and that's the version that we got with the report down here that we've already done.
So let's go ahead and organize it by guest this time. I'll make that selection and press Next. Then we can add any additional grouping levels based off of the data that's in the query. For instance, if I group it by last name, the report will group all of my guests with the same last name together, and then it will have their first name and phone number, followed by the check-in details for that person, and then the next first name and phone number for the same last name, followed by their check-in details. Now I'm not expecting too many of my guests to share a last name, so we're going to actually move that out, and just keep it without any additionaL grouping.
Go ahead and press Next. On this screen, we get to define how our records will be sorted. I'm going to sort them based off of the check-in date, but I'm gonna do it descending, so click that button until it says descending, and that'll give us a reverse chronological sort. Press Next. Then we get to define the layout of our report. We have the options of stepped, block, or outline. You'll notice that this little graphic over here changes to represent the different selection that we've made here. I'm gonna leave it on stepped. We can change our paper orientation from portrait to landscape if we like, but I'll leave mine on portrait.
Finally, we have this checkbox at the bottom that says "adjust the field widths "so that all fields fit on a page." Now I'm gonna leave this checked, but honestly, it doesn't really seem like it does a whole lot. You can experiment with having it either off or on. I'll go ahead and just leave it on for now. Go ahead and press the Next button, and finally, we get to title our report. I'm going to say that right over the other report that we already created check-in details, so let's go ahead and just name it the same thing. At the bottom, we get to decide whether we want to preview the report, or go into a design view. Let's just go ahead and take a look at what the report looks like.
I'll jump straight into print preview mode with this first option, and press the Finish button. Access is going to ask me if I want to overwrite the existing file that we did, so go ahead and press Yes, and then it opens up imprint preview mode. So this is admittedly not a useful layout at this point, and you can see that having that checkbox turned on so that all the fields fit didn't really do us a whole lot of good over here because most of our data is being truncated anyway, but the details are here in the report if you know what you're looking for. Here we have the first name, and then you can see that it's sorted alphabetically based off of the last name of our guests.
So we have Martin Adams, his phone number, and then below that, we have some of his check-in details. So this is where the date would appear if it was big enough to hold the date, and then we also have the rate code for the room that he was checked into. If we scroll down, we can see that Martin Adams has quite a lot of check-in history with the hotel. When we run out of details for him, we jump to the next person, Anne Alvazez, and we can see her check-in details below. When we get to the bottom of the page, we have a page footer here. We can go over to the next page, and back at the top, we have more details for Anne Alvarez, and if we scroll down, when we run out of details for her, we jump to Betty Anderson.
So even though this is the same data as the report that we used in the previous movie, the report has a very different look. Now each guest is listed only a single time, and their check-in history is listed below. However, it's pretty clear that this report needs some major surgery in order to be useful. We could continue working on it in the design and layout views.
- Determine the essential uses for the Trust Center.
- Explore the functions of the database Navigation pane.
- Recognize the fundamentals of entering data when using Access.
- Identify the necessary steps when importing a table when using Access.
- Break down the fundamentals of filtering and sorting table data in Access.
- Identify the method utilized when building queries in Design view.
- Determine the role of forms in Access.
- Examine all of the elements involved in maintaining a database in Access.
- Explore how to properly protect an Access database with a password.