Join Adam Wilbert for an in-depth discussion in this video Building reports from queries, part of Access 2013: Forms and Reports in Depth.
- View Offline
- Building a report off an existing query is probably the easiest way to get a great looking report that displays some fairly complex information from your database. By leveraging the power of queries and feeding their results into the report's record source, you'll create a good foundation from which the report design becomes just a matter of dragging and dropping fields into the appropriate locations. I've said this before about Access, but sometimes all of the wizards and automated object creation features of the program can really get in the way and make things more confusing than they really are. Once you understand what's going on behind the scenes, it's often the case that hand-built from the ground up, really is the way to go.
Now for this exercise, I've gone ahead and pre-built a query that totals up our customer's lifetime spent with Kinetico. That query is right here called Customer Lifetime Sales. If I double-click on it we can run it. Here we have a column for CustomerID, the first and last name of the customer, and a total of how much money they've spent with Kinetico, as well as their StateName that they're from. If I view it in Design View, we can see that we're using the Totals row here, to add up all their suggested retail prices of each order. Let's go ahead and close this down and build a report off of this.
I'm not gonna save any changes to it. Let's go to the Create tab, and in the Report section I'm gonna click on Report Design. Now just like with forms, the first thing we need to do is connect it to a data source. I'm gonna switch over to the Design tab and open up the Property Sheet. On the Data tab we'll find the Record Source property, and using the drop-down list we can choose the Customer Lifetime Sales query that I was just looking at. Now we can set up our grouping and sorting levels. We'll come down here and add a group, and if this section isn't turned on, it's the Group & Sort button up here.
We'll go ahead and toggle that on and go into Add a Group. For this report, I want to group all the employees that are in the same state together. So I'm gonna choose to group on StateName. Then we can just start adding our fields. We'll come back up here to the top to add existing fields. I'm gonna find the StateName field here and I'm gonna drag and drop it up into the StateName header section that just appeared. The rest of these fields will go into the Detail section. So I'll click on CustomerID and and Shift click on Lifetime Sales. Then I'll just click and drag to drop them down here.
Now we can start arranging our form. I'm gonna start up here with the StateName label. This is where our state name is going to appear and I don't need a label to tell me that's the state. I think that'll be obvious, so let's go ahead and get rid of that label and move the StateName field over. Also we're gonna make it a little bit wider, and then we'll drag this section down a little bit. Next I want to put my customer information down below here in the Detail section, but I don't need labels down here each time. So let's go ahead and move these labels up into the StateName header section. So every time we move to a new state, we'll have the additional labels.
Now if you click on them you might remember that in order to move the label separate from the control it's attached to, you need to use this handle in the upper left hand corner. But if you try to move it from the Detail section up into the StateName header, you'll notice that it won't move across that bar. What we can do is actually highlight all of these here by selecting a box around them and then press Ctrl+X on your keyboard to cut them to your clipboard. Then I'll come up and click on the StateName header, and press Ctrl+V to paste them in. Now let's just rearrange them. I'm gonna click and drag them down to about here. We'll do the CustomerID, then the FirstName, then the LastName, and then Lifetime Sales.
Now you might notice these little green flags that are appearing in the top corner, and this little flag here, this warning message, that says that this label is not associated with a control. And that's okay because they're in a different section now, they're not connected anymore. We can actually just press this down arrow here and say Ignore this error. I'll go ahead and do that for all of these controls. Great, let's go ahead and move this Detail section up and align the text boxes. This is where our data will appear, below the labels that appear above. We'll take the FirstName and put it here, and our LastName, and then our Lifetime Sales.
Let's go ahead and go down to the bottom of the section, and I'm gonna find where it says Page Footer, and I'm gonna drag that up to make my Detail section very narrow. We'll go ahead and drag it up, and we'll make it tight against the bottom of these text boxes. So now let's go ahead and see what our report looks like. I'll switch my view here to Print Preview. So already we've got a pretty clean and legible report going on here. We've got our Alabama data here, and then within Alabama we have all of the customers that live in Alabama, along with how much money they spent with the company. If I scroll down, we'll see that we have some Alaska customers, and here's a couple of people that live in Alaska along with what they've spent.
And then we have our Arizona customers, and so on. At this the point, the functionality is what we're looking for and it's working out just fine. Now it's time to start taking a look at some of the refinements that we can make. Let's go ahead and scroll back up to the top. First I'd like to make this a little bit wider, so that I take full advantage of the sheet of paper that we're gonna be printing on. Next, I'd like to remove the boxes from all of these data fields. Then, I want to sort my Lifetime Sales descending, so that the highest spending customer in each state will appear at the top of each list. And finally we need to change these labels a little bit so they appear on each of these data columns.
So let's go back into Design View and make those changes. I'm gonna go ahead and close Print Preview here. First let's make our report wider. I'm gonna go ahead and close this Field List here, and I'm gonna drag the edge of my Report section out to about 7 1/2 inches. Then we can start moving these boxes around. Lifetime Sales is where our Lifetime Sales values will appear, so I'll move those over here to the right, and I'm gonna make this a little bit wider. I'm gonna move this Detail section down so I have a little bit more room to work here. We'll click on LastName and I'm gonna spread that out and make it a little bit wider as well. Then our FirstName, I can make that wider.
And then our CustomerID, I'll make that a little bit narrower here. Let's start working on some fonts. Let's go ahead and change our CustomerID font. I'm can go up to Format. I'm gonna make this a little bit lighter gray. So go ahead and choose this color down here. And I'll make it a little bit smaller, I'll make it nine points. Then I wanna take a look at my Lifetime Sales values. I want to make sure that those are formatted as currency values, so I'll select it, and then up here in the number group, I'll select this dollar sign here to apply the currency formatting. Next I want the labels to appear directly on top of the column.
So let's go ahead and select this CustomerID and I'll select this one here, and we'll go over here to the Arrange tab, and I'll make them Size/Space, To Widest to make them the same width, and then I'll say Align to Left to make sure they line up right against each other. Let's do the same for the rest. Let's go ahead and select the FirstName text box and let's associate a label, Size/Space to Widest, and then Align Left. Do the same thing with LastName here, Size/Space to Widest, and then Align Left. And then finally Lifetime Sales, select both of those, Size/Space to Widest, and I'm gonna Align this one to the Right over here.
Actually I need to move my LastName back over to where I started. I'll just use the arrow keys for that. Next we can work on our StateName. Let's go ahead and change this formatting. I'm gonna make it bold and I'm gonna make it a little bit bigger, let's say 14 point. We'll make this text box a little bigger, and we'll apply a color to the background here. So I'll select the StateName header section by clicking anywhere in the background. We'll go to the Design tab and turn on our Property Sheet. We'll go to the Format tab, and we'll go ahead and change that background color. Right here it says Background 1. I'm gonna use this Build button, go to More Colors, and then Custom, and we'll type in our custom color for Kinetico, zero, 114, and 139.
We'll go ahead and say OK, and now we'll make this box here transparent so it doesn't have a white rectangle around it. Then to the Format tab, Shape Fill, and Transparent, and we'll make all these text labels white. So press Shift to select all of these. We'll go to the Format tab, and we'll change our font color here to white. Finally let's make these a little bit more human legible. I'll take this CustomerID and I'll add a space here between "Customer" and "ID." And we'll do the same for FirstName and for LastName here. Finally we wanted to add our Lifetime Sales so they're sorted properly.
So we'll come down here into the Group and Sort section, and we'll add a Sort based off of Lifetime Sales. But then I want to change it from "Smallest to Largest" to "Largest to Smallest." That way they sort descending. Let's go ahead and collapse our Detail section, and we'll take one last glance at it and everything looks good. So go over here to the Home tab and I'll switch my view into Print Preview. So that's looking pretty good. The last thing I want to do is remove these boxes here. And I want to make sure that the Lifetime Sales label lines up exactly with these data values down below. Because the data values are numbers, they're gonna line up against the right hand side of the box, whereas the Lifetime Sales label here on the top is against the left hand edge.
Let's close Print Preview and we'll make those changes. First I'll click on my report anywhere, I'll press Ctrl+A to select everything, we'll go to the Format tab, Shape Outline, and Transparent. And then finally I'll go to Lifetime Sales. First I'll deselect everything, then I'll select both of these, and I'll go to the Format tab and turn their text alignment to the right. Let's go ahead and save this report. I'm gonna go ahead and save it as Customer Lifetime Sales, go ahead and say OK, and we'll take a look at our final result. Go to the Design tab, and Print Preview.
So there's the start of our report. We can scroll through to see all the records. Here's Alabama and our customers, and their lifetime totals are getting sorted appropriately. I can scroll down here, and then we have Arizona. And it looks like we have Alaska here and that's actually in a gray box instead of in this green color. That's because by default, these sections have an alternating row color. Let's go ahead and fix that really quick. Go back in to close Print Preview, and that'll take us back to Design View. I'll click on the State Name header section back here and I'll look for this Alternate Back Color property. I'm actually gonna change that to No Color. That'll make sure everything is the same back color.
It doesn't alternate for every other section. All right, Print Preview, I'll press the Save button, and now everything is looking good. So with a little effort, we've already got a pretty respectable looking report if I say so myself. By starting from scratch, we didn't have to tweak any of the pre-made pieces that the wizards tend to put in, and were able to make it to our specifications right from the start. In the next movie, we're gonna continue building on this report by looking at the conditional formatting rules that we can establish for our data.
Then the course dives into reports: creating efficient and readable layouts, grouping data into categories, tying reports to queries, and using conditional formatting rules to highlight key takeaways from the data. Finally, Adam demonstrates how to link forms and reports together and print your results, and introduces unique ways to save time filling out paperwork and generating form letters.
- Creating forms with the Form Wizard
- Aligning form controls
- Adding buttons, links, and attachments to forms and reports
- Building database navigation elements
- Grouping and sorting data in reports
- Building reports from queries or wizards
- Calculating fields
- Linking forms and reports
- Printing and exporting reports