Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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 the results into the report's record source, you'll create a good foundation from which 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 just make things more confusing than they really are. Once you understand what's going on behind-the-scenes, it's often the case that the hand-built from the ground up really is the way to go.
In this exercise, I've gone ahead and pre-built a query that totals up our customer's lifetime spent amount with the Two Trees Olive Oil Company. Let's go ahead and open up TotalSalesCustomers query. I'll double-click on it to open it and we'll see that we're getting our first and last name of the customer, the total amount of money that they spent with the company, and the state that they are from. Let's take a look at this query in Design View. I'll close the Property Sheet over here to give myself some room. So I've got my four tables that I'm building this off of; Customers table, the Orders table, the Products table, and the States table. From those four tables, I'm pulling the first and last name, the price, and the state name.
And on Price, I've got the Totals turned on and I'm adding up the values with the Sum function here. So this is the data that we're going to be using in our report. I'll go ahead and close the query. We'll start creating a new report from scratch. I'll go to the Create Tab and inside of Report & Design view. Now, just like with forms, the first thing I want to do is connect it to a data source. I'll go ahead and go to Property Sheet, we'll look at the properties of our report, and we've got this Record Source property here. I can use the drop-down menu and choose the query TotalSalesCustomers.
At this point, we can go ahead and start setting up the sections of our report. We'll go to the Design Tab, the Group & Sort section, turn that on, and then we'll add a group. I'll click on Group; I'm going to group this by state. Go ahead and click on the StateName. That will add a StateName Header to our report up here at the top. I'll also add a sort. I'll add a sort and I'll sort the values within the states by the Lifetime Sales or the total spent amount. Let's go ahead and turn off Group & Sort to get rid of that window and I'm going to scroll down. Now, we've got that Detail section to make it a little smaller, here and I'll drag this up.
Now I can see everything on one screen. At this point, we can just start dragging-and-dropping fields and dropping them into our reports. I am going to go to Add Existing Fields. Since I've connected this to a data source, these are the four fields from that query. So I'll take FirstName, LastName, and Lifetime Sales, and drop them into the Detail section. I can just double-click on each one. I'll take StateName and I'm going to drag-and-drop it into the StateName Header, here. Now, let's go ahead and rearrange these a little bit. I'll type FirstName, and I'll drag it and I'll put it up here at the top, take LastName, put it at the right, and I'll drag Lifetime Sales and I'll put it to the right of that.
Now the labels that came with them, right now they're overlapping a little bit. I can have those repeating every time I have a new state, I will have the labels with the columns of data below. But unfortunately, you can't move these labels just directly across from the Detail section to the StateName Header. You try and drag them using the top-left anchor, it'll just hit that wall, and it won't move across. So what we can do is copy and paste them up in the State Header section. I'll select each of the labels; the FirstName label, and I'll Shift+Click on LastName Sales and this FirstName label here. I'll press Ctrl+X to cut.
I'll select my StateName Header section and press Ctrl+V to paste those in. Now, we'll just line them up. I'll move Lifetime Sales right about there, the LastName field, I'll move it right about there, and the FirstName one, I'm going to move that right about here. I'll move my StateName Header up and I'll align these underneath. Now, we can go through and fine-tune the alignment as we continue to further refine our report. Let's go ahead and just take a look at the functionality. But, before I do that, I'm going to take this Detail section, I'm going to make it really narrow, and we'll take a look at this in Print Preview Mode here.
Now, I can see I've got pretty respectable looking report already. I mean, the fields are in the right spot, essentially I can move them around and refine the layout. But, it's giving me the results that I was looking for. I've got the StateName here, Alabama, and I've got my customers sorted underneath. So I've got Rylee Whitfield, and the Lifetime Sales. It looks like I've got this reverse though from the way I really wanted it. I wanted the highest value at the top, and the lowest value at the bottom. So I can go and change that in the Grouping & Sorting options. Close Print Preview, I'll turn on in my Design Tab, Group & Sort, we'll take my Sort by Lifetime Sales, and I'll change it here.
Now, let's add a couple of other items to my report. We can add a title into the Header section here using a label. I'll click on Label, click there once to type in the title. Title for this is going to be Lifetime Sales by State. I'll press Enter. Now, I can format my title. We'll change the font to 22. I'll change the color from gray to this darker gray here to be 15% dark. I'll make this section a little bit bigger, and I'll make my textbox bigger. We can also add lines to help break up this space. I'll go to my Design Tab, and grab my line object, here.
I'll add a line to the StateName Header. So every time I have a new state, I'll have a line that's marking out that space. I'll click-and-drag out a line. We'll go ahead and rearrange these a little bit. I don't necessarily need a label for the StateName, it will be obvious that, that's the StateName. So I'm going to delete the StateName label and I'll move the StateName field over. I'll go ahead, and format that, I will make it bold, and then I'll make it a little bit bigger here. I'll take these values here and I will line them up. I'll click on each of these LastName fields, all six at once.
I'll go to the Arrange Tab>Size/Space>To Widest, to make them all the same size, then I'll line them up all against their left edge. So I'll select these two, Align>Left, these two, Align>Left, and these two, Align>Left, and maybe I'll move the LastName over a little bit. Okay, let's take a look at our design. Go to the Design Tab, and Print Preview, and there's our design looking up. I am going to go ahead and get rid of those boxes.
We'll close the Print Preview. I'm going to press Ctrl+A to select everything. We'll go to Format>Shape Outline> Transparent to get rid of the boxes around everything. Let's take a look at the Final Layout here; Print Preview, and that's what our report is looking like. So with a very little effort, we've already got a pretty respectable looking report. By starting from scratch, we didn't have to tweak any of the pre-made pieces that the wizards tend to put-in, and we're able to make it to our specifications right from the start. In the next movie, we're going to continue building on this form by looking at the conditional formatting rules that we can establish.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91082 Viewers
80 Video lessons · 138149 Viewers
59 Video lessons · 56960 Viewers
52 Video lessons · 70592 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.