Grouping
By Richard Rost
4 years ago
Sorting & Grouping in Microsoft Access Reports
In this video, I will teach you how to use Sorting & Grouping Levels in your Microsoft Access reports to group like information together.
Adam from Newport, Vermont (a Platinum Member) asks: I want to prepare a nice report showing the contact history for each of my customers for the past year. I know how to create a query to link together the customer table with the contact table so I can show the results I want that way, however is there any way to group all of one customer's contacts together so I don't have to keep seeing his name repeat on each line? Thanks.
Members
Members will learn how to apply the same techniques to our Order Entry system. We will learn how to print multiple invoices at the same time. We will print all invoices for a customer, all unpaid invoices for a customer, or all unpaid invoices for all customers. This is great for doing monthly batch invoicing.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Suggested Course
Links
Member Link
Keywords
microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, group, grouping, grouping level, sorting and grouping, sort group, sorting and grouping level, group by, Create a grouped or summary report, sorting and grouping data in reports, sort and filter groups, group in reports, How to Group a Report in Access, Grouping and Summarizing Data in Microsoft Access Reports, how to create a summary report in access
Subscribe to Grouping
Get notifications when this page is updated
Intro In this video, I will show you how to use sorting and grouping levels in Microsoft Access reports to display related records together for each customer, such as showing all contact history for a customer without repeating their name on every line. You'll learn how to join customer and contact tables in a query, set up the report layout for clarity and efficiency, and use Access's grouping tools to arrange and summarize your data. I'll also demonstrate common formatting techniques and how to add a simple total for the number of contacts per customer.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to use sorting and grouping levels in your Microsoft Access Reports.
Today's question comes from Adam in Newport, Vermont, one of my platinum members. Adam says, "I want to prepare a nice report showing the contact history for each of my customers for the past year. I know how to create a query to link together the customer table with the contact table so I can show the results I want that way. However, is there any way to group all of the customers' contacts together so I don't have to keep seeing his name repeat on each line? Thanks."
Well, yes, Adam. Of course, we can do that. In a report, it's called a grouping level. We'll group together all of the customers' contact records so you'll see the customer name once and then all of his contacts in that group. Let me show you how to do it.
Before we get started, there are three videos I want you to go watch if you haven't already. Watch my blank template. That's where I create my TechHelp free template. Watch my contacts video. That's where I build the contacts portion of that template so you know what the customers and contacts are. And go watch my free relationships videos so you understand relating two tables together. These are all free videos. They're on my website. They're on my YouTube channel. I'll put links down below that you can click on in the description. Go watch these first. Then come on back.
So here I am in my TechHelp free template. This is a free download. You can go grab a copy on my website if you want to. In this database, I've got a customer list. There's all my customers. Each customer has a customer form, and each customer can have associated with him multiple contacts. For example, here's James Kirk. He fought the Klingons. He destroyed the Enterprise. Then on this date, he sold some tribbles and then bought a new phaser and beamed down with Scott. So he's got a bunch of contacts.
Maybe under me, what do I have in here? I got the call for a job. I came in for an interview, got hired, whatever. You track the contact history with each of your customers. Now, what Adam wants to do is put together a report showing each customer, but only showing the customer's name once and then showing all that customer's contacts underneath.
We're going to start with a query to join those together. So, create and then query design. Let's get rid of the property sheet. We're going to bring in the two tables that have the data that we want in them. I want the customer table and the contact table. I close that. Now, because I have two fields, both named customer ID and they're both long integers - this one's an autonumber, which is a type of long integer - Access makes this ad hoc relationship between those two tables. It knows that this customer ID is the same as that customer ID.
We're going to bring in the stuff that we want in the report. I also like to bring in the IDs. We don't necessarily need them because we don't want to see them in the report, but sometimes they're handy for grouping things together, like the same customer ID. You might have two Jim Kirks in your database, which will only have one customer number three. So, I'm going to bring in customer ID, and then first name and last name, whatever else you want from the customer table. If you want to put his phone number in there too, that's fine, but I'll just bring that stuff in. Then over here, bring in the stuff you want from the contact table. If you want the contact ID, sure, it won't hurt, but all I care about is the contact date, the description, and the notes, let's say.
I'm going to save this, Control S, as my customer contact Q, a customer contact query. If you want to put some kind of criteria on here, like I noticed that Adam, you said you wanted it in the past year, just put that here in the criteria for the contact date. You could do greater than; if you want a simple year, you could say today's date minus 365. Or if you want an exact calendar date, you could say greater than dateadd, right, we're adding a year, yy, comma, minus one - that subtracts a year from today's date, just like that. I covered this in my dateadd video. Let me zoom in so you can see it. That's an exact calendar year.
But I'm going to get rid of that because we want all of them just for this video, because I want to be able to see all the contacts. So again, save that and then when you run it, you get that. I can see there's three contacts for me, there's five in there for Jimmy Kirk, and there's one for Deanna Troy. But I don't want this to print out like this. I want it to show my name once, then my contacts underneath it: date, description, and notes in there, and then Jim Kirk and then his, and so on. I don't want to see this whole thing repeat like a spreadsheet. I want a nice printed report.
We need this query to get our data together, but now the query is finished. Let's close that. Now, there is a wizard that does this report for you, but I don't like it. I find myself having to spend way too much time demodifying the report and taking out formatting and all the nasty stuff that the report does. I think it's easier to build this from scratch.
If you built my blank template with me, I have this thing here called blank R. This is basically my blank report that we built in that template. It's just real simple, all the formatting and stuff that I want. Let me show you that in a minute. So, let's just copy this guy. Come over here. We're going to copy this, Control C, Control V. There we go. We'll call this my customer contact R, my customer contact report.
We're going to right-click, design view. I like using design view. I think this is better, like I do forms this way too. As you've seen if you've taken any of my previous videos, I'm not a big fan of the wizards for developing tables and forms and stuff like that. Once you get the hang of it and you learn how to do it from scratch, it's actually easier and faster than going through the wizard.
The first thing we have to do is bind this report to its data source. To open up the report's properties, right here where those ruler bars meet, double-click, go to data, and you'll find the record source property. Drop that down. We want our customer contact Q. You see that's why I put Ts and Qs on the end of my tables and queries, because now I know that's a query. So, we're done with this now. Now this report knows what it's getting its data from.
Let's go to report design and find Add Existing Fields. Now we have these fields that show up here because the report knows where it's getting its data from. So, these are the fields from that query. Let's add them all in. I'm going to click on the first one, hold down the shift key, click on the last one, let the shift key go, and then click and drag any one of them and drop them here in the detail section.
The only reason I keep this little guy around is because I use it for format painting, because it's got no border and it's got a black label as opposed to the stupid border and the gray around here. So I just click on this. Watch this. I'm going to go to the format painter, which I put up here on the Quick Access Toolbar, double-click, and then go click, click, click, click, click, click. Got all the formats that I want. Turn it off and now I can get rid of you. Goodbye. Oops. That's all I keep him around for.
Let's slide this stuff over this way and see what we have here. Save it. Let's go to print preview. I like to right-click here and go to print preview. That's what it looks like. It looks like a single report. We have one here, another one there, another one there. I want to lay it up in more of a grid format.
So, let's go to design view. Let's move the labels. Take these labels, cut them out (Control X), and put them in the page header. Paste like that. Then we'll just align them like this. Real simple. In fact, let's get rid of the notes label. You'll see how I deal with notes in a second. Shrink this page header section up like that and then just line these guys up underneath them. Like so.
Learning how to do this formatting - I mean, it seems kind of boring watching me do it, but take your time and practice it, and once you get good at it, you'll see how easy it is. I want to put notes underneath description. Put it about there. Let's slide this detail section up like that. Save it (Control S), and then print preview again. Again, I have print preview up here in my Quick Launch toolbar. There it is.
Looking better. Contact date is too narrow. We have to make that wider, that's fine. I don't need to see the customer ID, so we can get rid of that. A couple more steps here. Back to design view. Let's just get rid of customer ID. Again, I want to have it in here in the data source so I can group by it, but I don't need to see it in the actual report.
Let's slide first name and last name all the way to the left, and contact date over here a little bit more. I'm trying to show you how this evolves - the evolution behind this. For the contact date, I want to change this to just a short date. Let's go to the format and change the format here to short date. We don't need to see the time unless you care. If you want to see, for example, if you're doing a report like a call center and you want to know exactly how long a call lasts and what time it came in, that's fine. For this, I just want dates. That's all I care about for my personal report here.
Again, close that. Let's slide this over a little bit more. The description is going to keep getting bigger and bigger. I'm also going to select all of these - see what I did there in the ruler? I teach all these little tricks in my full classes, by the way, so that's why it seems like I'm going kind of quick - select all of those like that with the ruler, right-click, and we're going to go size to grid. There are a bunch of grid dots in the background to line all your objects up so they're nice and neatly on top of each other like that.
One of my pet peeves with Access is when you bring in new objects into the report or form, it doesn't automatically size them to the grid. I hate that. Microsoft needs to change that. I think I put that on the Microsoft suggestion board several times.
Let's see what we have now. Side you over real quick, get over there. Save it, and print preview. Looking better. We need to line this up to the left. By default, dates line up on the right, so let's fix that real quick. Take that and align left under format, align left. Let's see. Much better. Good. We're starting to get there.
Now, this notes field that I have down here underneath it. Look at how this is taking up a proper amount of space, but all the rest of these have this big giant space there because there's an empty notes field. There's no notes there. If someone does have notes, let me save this. Let's go back to somebody else. Let's go back to me.
If I do have notes, let's say I got hired. Down here is the notes field. These are some sample notes. Let me make this really, really large too. Copy. So there's a bunch of notes in there. If I go back to my report, print preview. Look at that, see? It took up all that space. That's because this can grow to fit the data that's in it. It can also shrink. I covered this. I have a separate video on Can Grow Can Shrink. Again, I'll put a link down below. You can go watch that one too.
If you look at the field itself, right here, the notes field, if you go to Format and slide down the bottom here, you'll see there are two properties: Can Grow and Can Shrink. Can Grow means it can grow vertically to get bigger to fit more data. Can Shrink means it can shrink up, including to nothing if there's no data in there. However, even though this particular field can shrink, the detail section itself cannot. The default setting is no. All you have to do is turn that to yes, save it, and now, right-click, print preview.
Look at that. All these guys here that have no data in there shrunk up, so there's no extra wasted space. That's nice. I covered that in my Can Grow Can Shrink video.
We're still getting to the grouping. I know I'm getting there. I want to show you all this other stuff first. One more little thing. I want to put a line underneath this heading here. We're going to make this just a little bit bigger. We're going to drop this down. We're going to go to the line tool, drop a line in here like this. I just draw it that big first, and then I grab it and move it where I want it like this. Then slide it over here. So, we have a nice little line. Maybe bold this stuff. Let's take a peek. There we go. Looking good.
Now, let's group this so that each one of my contacts shows up in one spot, then all of Jim Kirk's show up in the next spot. How do we do that? Back to Design View. Up here on the ribbon, under Report Design, find this thing over here called Group and Sort. It's under Grouping and Totals. Group and Sort. Click on that. This bar appears down here. Not the kind of bar where you get beverages. Big, gigantic bar right here. You can add two types of levels: a grouping level or a sorting level.
Let's do the group first. Add a group. You get this new bar here. What do you want to group by? I want to group by Customer ID. So that's why I said it's important to have that customer ID in the query. Customer ID means all the stuff with the same customer ID is going to get grouped together. From smallest to largest. That doesn't matter. We're going to put a sort on in a minute to sort it so they're alphabetical, so you can ignore that for now.
Hit this More here. Now, it's going to be by entire value. There are some options. If you get numbers, you can go by intervals. If it's a date value, you can go by week, by month, by all kinds of different stuff.
Do you want totals on the bottom? I like to do manual totals, so ignore that for now. I have a whole separate video on how to do totals.
So, by default, you get a header. You can see right there I got customer ID header, but you don't have a footer section. That's fine for now. We don't need that right now.
What we're going to do now is we're going to take our first name and our last name and move them up here in the customer ID header. All the customer stuff is going to be grouped underneath that. So you're going to have customer and then that customer's contacts.
So, I'm going to close this. Don't hit this X, by the way. This is confusing. I get a lot of emails from people about this one. This X actually deletes this bar that you just created at this grouping level. You don't want to hit that one. You want to hit this one to close the sorting and grouping pane. Let's call it pane, like the Navigation Pane. That's the sorting and grouping pane. It's a big pane. I know.
Take a peek. Oh, look at that. There is the group for Richard Rost. Now you have this stupid alternating background color stuff going on. I hate that. I'm going to turn that off in a second. But now you can see at least all of Jimmy Kirk's stuff is together here, and then Deanna Troy's got one down below here.
Let's get rid of that color. Whenever you create one of these new sections, you get this alternate background color. I hate this. Go away. No color.
Let's also put a line underneath each customer. To do that, we'll need the customer ID footer. So go back to grouping and sorting, hit the More right here and go with a footer section. Now you can see we have a customer ID footer. So under each customer, we can put stuff. Usually, you'll use that for totals and stuff.
So, I'm going to copy this line that I made up here. Copy. Click down here in the ID footer. Paste it. You can see it's right there. It's right up on top. Just click and drag it down a hair, like there. Just leave a little bit of room. You see it's there. There's my footer section.
I think we still get that stupid color. Yes, oh, these colors. I hate that. Save it. And preview. And look at that. See? Here's our page header. You get Richard Rost, the customer footer right there, the next customer, and then down here. There you go. Save.
Let's make the name look a little better instead of Richard Rost like that. Let's make it look pretty. So, let's go back into here. I'm going to delete last name and I'm going to make the first name box a lot bigger like this. Let's open up its properties. Let's go to the All tab. Let's change the name "First Name" to "Full Name." The control source is now going to be: =LastName & ", " & FirstName. Let me zoom in so you can see that better. Shift F2.
= [LastName] & ", " & [FirstName]
This is concatenation. I have a whole video on concatenation if you don't know what this stuff is. It just smashes these together, so it'll be "Rost, Richard." Hit OK. While I'm at it, let's make this look nicer too. Let's go 18 point and maybe bold it. I can get rid of this now because it's pretty obvious. Let's save it, close this, and take a peek. That looks nice. Look at that. All right. Looking pretty good.
Real quick word from our sponsor - that's me. If you want to learn more about sorting and grouping in Access, my Access Expert Level 12 course covers sorting and grouping levels in detail. Lots and lots of detail. All kinds of cool stuff. We group customers by country and then by state, we do counts and all that stuff, credit limit sums, talk about date part, we do weeks, hours worked, all this kind of cool stuff, grouping together by numbers and currency. There's so much stuff covered in this class, I don't even remember all of it. Again, that's Access Expert 12. I'll put a link down below. Click on it if you want to learn more.
So, back to our stuff here. Let's put a sort on this. So, right now it's Rost Richard, James Kirk, Deanna Troy. I want these sorted alphabetically by last name. We'll go last name, then also first name, because if I have Jim Kirk and then I have Dave Kirk, Dave should be on top, obviously.
So, let's go back into design view. Let's turn on sorting and grouping. Now we're going to add a sort down here. We're going to sort by last name. But here's the problem: the grouping level is going to go first. So, if I just leave it like this, watch what happens. Look, it's still Richard Rost, James Kirk. Why? Because the sorting level is under the grouping level.
We need to move that. We need to move the sort so it's above the group. So, it sorts first, then it groups. Make sense? Come back in here. Take this sort by last name bar and we're going to click this little button right there to move it up. See? Now the sort by last name is before it groups by customer ID. While I'm at it, I'll add a sort for first name as well and I'll move that up once. So it goes sort by last name, then sort by first name, then group by customer ID.
Now I can close this pane. Now save it. Let's go to preview. Look at that. Perfect. Kirk James, Rost Richard, Troy Deanna. Perfect.
You can put a header across the top of the report. For example, put a label up here and go "Customer Contact Report," or whatever you want to call it. Format it. Make it big. Make it colorful, if you have a color printer and want to waste ink. Whatever you want to do to make it pretty.
I honestly think you don't need these labels either. I would get rid of this because at this point, these labels are kind of meaningless. I'm going to take the line here and move that up here, so it only shows once on the top of the report. I don't think we really need a page header for this one. If you want to put a page footer on the bottom with the page numbering and all that stuff, I cover all that in my basic reporting class. I think that's an Access Beginner 1 - my free lessons.
You can do all kinds of stuff. You can change the font. You can make it bold. You can make it bigger. Whatever you want to do, sky's the limit. That's why we're building with Access, because we want to make things look exactly the way we want. Save it and preview. It's looking really good.
There are all kinds of other settings in here. You can force the group header to stay with the first detail. You can keep the whole group together on a page. I cover all that stuff in my Access Expert 12 class.
One more thing: bonus material. I promised you I'd show you how to do at least one little total. Let's say you want to get a count, the number of total contacts that each customer has. In the footer for that group, we're going to count it for each customer, down under here. Put a text box. We'll put in here "Number of contacts," like that. I'll leave this gray. Let's slide it over here.
In this text box, open it up and the name is going to be "CountContacts." In here, we're going to put: =Count(*)
That says just count all the records inside this group. Save it, preview it, and there you go. There's five, and down here you get three and one. You can see why I have my other guy with the borders off and stuff. We're going to go in here and fix that. To turn that border off, you have to click on it, go to Format, go to Shape Outline, Transparent. That's a pain. Maybe we'll left align that too.
Save it, and now preview it. There you go. There's your total contacts for each person. That might look better on top of that line. Let's go back in here. Let's do this. Put the line below the contacts like that. That makes more sense. We'll slide this up. Save it. Preview it. Boom. Five contacts. You can do the same thing with SUM, AVERAGE, all those functions. Again, I cover all that in my full class. That's just to give you a little sample, a little bonus material there, of why you should sign up for my classes.
If you ask, these TechHelp videos are great. They give you little nuggets of information. If you want stuff prepared and taught thoroughly to get you all the information, that's what my full class is. This is just because I like you.
If you want to learn more, in the extended cut for the members, we're going to use grouping levels to print multiple invoices. If you've used my TechHelp database before, you know that there's an invoice you can generate for each order. The way it's set up now is it only generates one invoice at a time. So, you have to have that order up on the screen with the order form to print that invoice. But we'll get rid of that. We'll use grouping levels so you can print multiple invoices in one run. You can print all invoices for a customer, all unpaid invoices for a customer, so you can mail them all at once, for example, or all unpaid invoices for everybody, so you can do one batch a month and print all the unpaid invoices in your system and mail them.
I'll also show you how to reset the page numbering so that each invoice, as it starts, will start a new page number one. That's all covered in the extended cut for the members.
Silver members and up get access to all of my extended cut videos, all of them, 200 plus and counting. Gold members get access to my code vault and you can download these databases.
How do you become a member? Click on the Join button below the video. Once you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free Expert class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you've finished the Expert classes. These are the full-length courses found on my website, not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming as long as you keep watching them. I'll keep making more, and they'll always be free.Quiz Q1. What is the main feature demonstrated in this video?
A. Creating relationships between tables in Access
B. Sorting and grouping levels in Access Reports
C. Importing data from Excel to Access
D. Working with macros in Access
Q2. Why should you include the customer ID field in your report's query, even if you do not display it?
A. Because it is needed for grouping records together
B. It must be printed in every report
C. Queries without IDs cannot run in Access
D. Customer ID always contains customer names
Q3. Which method is preferred by the instructor for building reports in Access?
A. Using the built-in Report Wizard
B. Copying and modifying an existing blank report in design view
C. Using external report tools
D. Writing SQL code only
Q4. What property needs to be changed so that empty notes fields do not take up space in the report?
A. Set the 'Visible' property to 'No'
B. Change the 'Can Grow' property to 'No'
C. Set the 'Can Shrink' property to 'Yes'
D. Use a different data type for notes
Q5. What happens when you add a grouping level by Customer ID in your report?
A. All contacts are displayed in a single list
B. Each customer's contacts are grouped together under their name
C. Only one contact per customer is shown
D. Customer ID is hidden from all views
Q6. If you want to sort customers alphabetically by last name and then by first name in a report, which action must be done in the sorting and grouping pane?
A. Add sort levels above the grouping level
B. Delete all sorts and groups
C. Add sort levels below the grouping level
D. Sorting is not possible
Q7. What function was demonstrated to count the number of contacts per customer in the report?
A. =Sum([ContactID])
B. =Average([ContactID])
C. =Max([ContactID])
D. =Count(*)
Q8. Why does the instructor suggest against using the default alternate background color in new sections?
A. It is required for sorting
B. It can make the report look cluttered or distracting
C. It is needed for grouping to work
D. It prevents the report from printing
Q9. What does concatenation accomplish in the context of displaying full names in the report?
A. Deletes extra spaces from the name
B. Combines last name and first name into one field, separated by a comma
C. Assigns a unique number to each name
D. Finds duplicate names
Q10. What are the Can Grow and Can Shrink properties used for on a report text box in Access?
A. They determine font color and style
B. They control vertical expansion and contraction of the field based on the content
C. They merge multiple fields together
D. They delete empty records
Q11. What is the purpose of adding a footer section to the Customer ID grouping?
A. To display group totals such as count of contacts
B. To hide contact records
C. To create extra space between records
D. To prevent the report from printing
Q12. What is described as a pane that lets you add or adjust grouping and sorting levels in Access Reports?
A. Format Painter
B. Sorting and Grouping pane
C. Navigation Pane
D. Property Sheet
Q13. Why does the instructor recommend learning to format reports using design view?
A. It is the only way to edit reports
B. It is faster and more flexible than relying on wizards
C. Wizards do not work on reports
D. Design view is read-only
Q14. What bonus feature is added at the end of the video regarding invoices?
A. Printing individual invoices using macros
B. Using grouping levels to print multiple invoices in one run
C. Adding password protection to invoice reports
D. Exporting invoices directly to Excel
Q15. Where can viewers find more in-depth instruction about sorting and grouping in Access reports, according to the instructor?
A. Microsoft website only
B. Access Expert Level 12 course
C. The Access help file
D. The report wizard pop-up
Answers: 1-B; 2-A; 3-B; 4-C; 5-B; 6-A; 7-D; 8-B; 9-B; 10-B; 11-A; 12-B; 13-B; 14-B; 15-B
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 covers how to use sorting and grouping levels in Microsoft Access Reports. I'm going to explain how you can use these features to create a visually appealing report that displays all contact history for each customer, grouping the relevant contacts together under each customer's name rather than repeating the customer information on every single line.
Let's start by laying the groundwork. If you are not already familiar with the basics of database templates, the structure of the customer and contacts tables, or how relationships work in Access, I recommend checking out my free lessons on those topics first. These classes are available on my website and YouTube channel and cover how to build a blank template, set up the contacts section, and define table relationships.
Imagine we have a database with a list of customers. Each customer can have several contacts recorded, such as calls or emails. The goal is to generate a report that presents each customer's name once, followed by the details of their individual contacts for the past year.
The process begins with creating a query that links your customer and contacts tables. Even though you might eventually want to hide certain identifier fields like the customer ID in the finished report, it's useful to include them in your query now, especially for grouping purposes. Include the customer's basic info (such as first and last name) and the relevant fields from the contact records, like the date and notes.
If you want to filter contacts to the past year, you can set criteria for the contact date in your query. For example, you can configure it to only display contacts occurring after a certain date using built-in Access functions. But for demonstration, I'll leave the date criteria off so we can see all the data.
Once your query is ready and you confirm it displays the appropriate contacts for each customer, you're ready to build the report. While Access does offer a report wizard, I usually avoid it because it applies unwanted formatting and can be cumbersome to adjust. Instead, I prefer building reports from scratch, often starting with a blank template I've prepared that has my preferred formatting.
To create the report, copy your blank report template and bind it to the query you just created. In design view, add all the fields you want from the query into the report's detail section. Arrange and format these fields as desired—adjust column widths, move fields, and apply formatting tricks like aligning fields with the grid to keep things neat. If you want to clean up the display, remove any unnecessary labels or fields from the detail section (such as customer IDs, once you confirm grouping will be handled elsewhere).
When displaying notes, consider setting the notes field's properties so it can grow or shrink depending on the amount of text entered. Be sure to also set the detail section itself to allow growing or shrinking, which will prevent extra white space from showing up in your report for records that don't have notes.
Once everything is laid out, it's time to set up the grouping. In design view, open the Group and Sort options under the report design tools on the ribbon. Add a grouping level based on the customer ID field. This will group all records for a specific customer together. Access allows a variety of options for grouping by value, intervals, or dates, as well as whether to display group headers or footers. The header is useful for displaying the customer's name only once at the start of their group.
Move the customer's name fields (or a concatenated full name) into the group header so each group is clearly labeled. You can add formatting touches in this section, such as increasing the font size, making it bold, or adding a dividing line below the header for clarity.
If you want to add a final touch to your report, include a group footer section so you can display totals at the end of each customer's contact list. For example, you might add a text box in the footer to count the number of contacts for each customer.
If you would like your report sorted alphabetically by customer name, you'll want to add sorting levels for last name and then first name and make sure they are positioned above the grouping level in the Group and Sort pane. This ensures the report is organized naturally for easier reading.
For further polish, you can add a title or other decorative elements in the page header, and you might consider removing repetitive labels that are no longer necessary once your grouping structure is in place. Additional options exist for controlling how groups are displayed, such as keeping all of a customer's records together on one page or ensuring a group header always stays with its first detail row.
As a small bonus, you can also use the group footer to add summary functions such as counting, summing, or averaging values for each customer. For instance, a count of total contacts per customer adds a nice touch.
If you want to go even further, in today's Extended Cut for members, I'll cover how to use grouping levels to print multiple invoices within a single report, allow batch printing for unpaid invoices, and reset page numbers for each group—handy for creating professional, multi-invoice print jobs from the same data set.
There are a lot more advanced features covered in my Access Expert Level 12 course. That course teaches in depth about sorting, grouping, and advanced reporting options: grouping customers by geographic regions, calculating sums, working with dates, and much more.
You can always find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Creating a query to join customer and contact tables
Selecting appropriate fields for the report
Adding date criteria to limit records to the past year
Saving and running the query for report data
Copying and renaming a blank report template
Binding the report to a query as its data source
Adding fields from the query to the report
Using format painter to apply consistent formatting
Adjusting field and label placement in design view
Modifying field format properties, such as date format
Removing unused fields from the report
Aligning fields using the Access grid
Configuring text alignment in report fields
Setting Can Grow and Can Shrink properties for controls
Editing the detail section to eliminate extra space
Adding and formatting a line to the page header
Accessing the Group and Sort pane in reports
Adding a grouping level based on customer ID
Moving fields into the group header section
Removing or adjusting section background colors
Adding a footer section to the grouping level
Inserting and positioning lines in the group footer
Concatenating fields to display full customer name
Adjusting control sources for custom name formatting
Sorting records by last name and first name in the report
Reordering sort and group levels for correct output
Adding a report title using a label control
Removing unnecessary labels from the report
Adding a count of contacts for each customer in the group footer
Formatting text boxes and lines for a polished layout |
