Customer Reports

Lesson 12: Create Reports, Lists & Mailing Labels

S M L XL FS | Slo Reg Fast 2x | Bookmark

In Lesson 12, we will learn how to create several different customer reports in Microsoft Access. I will show you how to build a simple customer report for one customer at a time, a customer list report displaying all customers, and mailing label reports for active customers only. We will walk through using the Report Wizard, adjusting layout and formatting in Design View, and discuss report options like paper size, margins, and exporting to various formats. We will also create a query to filter active customers for mailing labels and talk about best practices for working with customer reports.

Navigation

Prev: Lesson 11
Next: Lesson 13

Keywords

Access Beginner, customer reports, customer list report, mailing labels, report wizard, print preview, report design view, grouping levels, sorting reports, report layout, move fields, print labels, isactive field, create query, landscape report, export r

Comments for Customer Reports

Age	Subject	From
3 years	Important Addendum	Richard Rost

Subscribe to Customer Reports
Get notifications when this page is updated

Intro

Transcript

In lesson 12, we're going to learn how to make a couple of different customer reports. We're going to build a simple customer report with one customer at a time. Then, we'll make a customer list report where we can see all our customers listed one per row. Then, we'll make some mailing labels, and we'll only send mailing labels to active customers. We'll learn about different things like paper size, margins, printing, and so on.

So far, we've learned how to build tables, queries, and forms. Now, reports are very similar in design to forms. However, reports are designed to be presented to someone who is not using your database. You can print them out, you can send them as email attachments, and you can do lots of things with reports. Forms are used for entering and editing data in the database. Reports are used for taking that information and displaying it for someone else.

Creating a report is very similar to creating a form. First, we pick the table or query that has the data in it. Then, we click Create. Over here, you'll see the Reports section - Table, Queries, Forms, Reports.

Just like forms have lots of different ways you can build forms, so do reports. There are lots of different things you can do to build reports. Here, we have the simple report designer. This is where Access just takes the table or query that you're pointing at and just makes a report for you. You get some options, but basically Access does it for you.

Then, you have Report Design, which gives you a blank report and lets you start from scratch. This is actually my preferred way to build reports. We'll cover that in future classes. We're just going to do some simple reports today.

Blank Report sets up the report for you and then lets you go ahead and move the fields around in layout mode.

There is the Report Wizard. I actually like the Report Wizard. I like a lot of the Wizards. Some of them are good Wizards, some of them are bad Wizards. The Report Wizard is a good Wizard. I like the Report Wizard. I'm going to show you the Report Wizard in a few minutes.

Then, we've got labels for making mailing labels and such. We're going to run through this too.

Let's start with just a simple report. Again, click on CustomerT and then click on this first one here that is just Report.

Now, Access throws together a basic report for you. I'm going to maximize this so it takes up the full Access window. This looks very similar to that multiple items form that we looked at earlier - the Continuous Form. If you scroll down, you'll see all your different customers down here. You can scroll to the right. This is a multi-page report. You'll see there are a couple pages here.

At the top, you'll see the title. You can double-click there and change it if you want - PC Resale Customer List. Press Enter. Over here, you'll see the Date and Time. Normally, for Forms, I don't bother putting Date and Time on there. But for Reports, you might want to know when this was generated. This is printed on this date. If you don't want the time there, click there. Press Delete and it goes away.

Just like Forms, Reports also have different Views. You'll see the Views are over here. If you drop this down, right now we're in Report View. Print Preview is the one you are going to want to pick the most. Print Preview will actually show you what this is going to look like when you print it. That will give you the best view of that Report. Down here, you can use the Navigation buttons to move between the different pages.

This isn't a very good Report. It's not very well-formatted. Like I said, Access just throws together a real simple Report for you. But that's not the best. I'll show you how to build a better one in a minute. When you're done with Print Preview, click the Close Print Preview button over here. That'll put you back into Report View.

There is Layout View and Design View. We'll talk more about Design View in future classes. This is where you can get in here and actually edit the fields and the labels and all that stuff. But remember, this is beginner level one. We still have our training wheels on. I can't show you everything in one class. It's coming. Don't worry.

Come back up top. Click on Design. For some reason, when you're going to Design View, it puts you into Page Setup mode. Don't worry about that just yet. Come back over here and go to Report View. Report View is the best way to view the data in the report on the screen. In Layout View, you can come in here and resize these columns or resize the rows.

I don't particularly care for this simple report that Access put together, so I'm going to hit the Close button up here. That will close this report. Do I want to save changes to this? No. That's OK. I didn't like it anyway.

Let's make the same kind of report, but I don't need all of those fields. I just want a simple customer list. So this time, let's use the Report Wizard.

The Report Wizard lets you pick what fields you want, what layout you want, all that stuff. It's based on the CustomerT. If you drop this down, you can pick any table or query. This is one of the reasons why back when I started using Microsoft Access back in 1994, it did not say table or query next to it over here on the left. So you did not know if this was just customers. You might have a table called customers and a query called customers. You did not know which one was which. That is why I personally started using that TQF naming system. And it just stuck with me. It just works. Plus, later on when you get into macros and modules and you do some VBA programming, it's handy to know if you're working with a table or a query based on the name of it.

I'm going to pick the CustomerT here. Now, what do I actually want to see on the report? Let's do CustomerID, FirstName, LastName, CompanyName, and PhoneNumber. You click on the field over here. You click on this little button that brings it over there. If you don't want it, you click on the button to send it back. That's for all of the fields, and that's to bring back all of the fields. Or you can double-click on something on this side. But that's all I want to see on this particular report.

Hit Next. Now, it's going to ask if you want any grouping levels. You can do something like group the customer list based on state. So all your New Yorkers show up first and all your Pennsylvania people show up. We're going to talk about grouping levels and sorting levels and all that stuff in future classes. Training wheels. We'll get there.

Next. You can add a sorting level if you want to. What do you want to sort by? Let's sort by LastName and then by FirstName ascending. Now, if you have a sort in a query, if you base this report on a query, this sort will override it. But if you are based just on a table, this is your only sort.

Next. What kind of report do you want? Tabular is the one we just had a minute ago that Access threw together for us. That is like a continuous form, a multiple items form. You get every record, one per row.

Columnar looks like our single form. We have the labels and the text boxes. You would have one customer per page, for example. There is Justify, which looks like an invoice.

For this, I'm going to pick Tabular. I'm going to go with Landscape so it goes this way instead of that way. Adjust the field width so all the fields fit on a page. That's kind of nice. Remember the other one was spilling over into three pages. There is a limit. Obviously, you can't put 700 fields on one report and expect it all to fit.

Next. What title do you want? Let's call this the CustomerListR. It's my list of customers.

Do you want to preview the report or modify the report's design? Let's just preview it. Hit Finish. There it goes.

Now that is a good wizard. I like that wizard. It put together a pretty nice-looking report.

Notice my mouse is now a magnifying glass. I can click to zoom in. There is your FirstName, LastName. CustomerID kind of came in there weird. I don't know why it's in that order. CompanyName, PhoneNumber.

Down here on the bottom left, you'll see the date. Over on the right, you have the page. You can also use this to zoom in and out if you want to. You have multiple pages or whatever.

Let's get this CustomerID out of here and move it over to the left in front of LastName.

Normally, I'm going to cheat again. Normally, I wait for level 2 and 3 to go into Design View. But like I did earlier with the customer form, let's go ahead and sneak ahead a little bit. Close the Print Preview.

Let's go over to Design View. I'm going to take this label and this text box and slide it over here before LastName. Notice in Design View, there are different banded sections. Forms have them too, but in reports they are a lot more prevalent.

Details is where all your details go. Page Header appears at the top of each page. Report Header appears once at the top of the report. Then you have the Page Footer at the bottom of each page and a Report Footer, of which right now there is nothing in there, but you can put stuff at the bottom of each report. Those are the different sections of a report. This is just a little sneak preview. I'll cover all of this in detail in future classes.

Right now, I'm going to take this text box, grab the edge, click, drag, and drop it right there on top of FirstName. Now, I'm going to take FirstName and slide it over to the right, just like that. Take LastName or FirstName, whichever one this is, and slide it over to the right. That's how easy it is to move things around in here.

Now we must do the labels too - the labels are on top. So take this, click, drag it over there. Now I'm going to click and then Shift-click on that one to select them both. Then, click and drag and we'll go to the right.

Save it. Close it. I'm going to open it back up again. If you double-click on it, it opens up in Report View. I do not like Report View. Report View to me... I like Print Preview. So I always right-click and then Print Preview. You can see what it's actually going to look like when you print it.

In a couple of classes, I'm going to show you how to make a main menu form with different buttons on it to open up your forms and reports. We'll have stuff open right up into Print Preview.

One more change I want to make. Look at that. CustomerID is kind of chopped off there. Numbers tend to line up on the right side, whereas text lines up on the left. Let's change the ID. Let's rename that just to ID. We'll make this all aligned to the left.

Close the Print Preview. Right-click. Design View. I want to change this label to just say ID. Just like that. Let's resize this like that.

Go to Format. Align Left. We'll do the same thing with the text box. We'll do Align Left. All of this stuff should line up to the left.

Save it. Close it. Right-click. Print Preview. There we go.

I apologize, my recording software is a little box, so I have to right-click. There you can see it's down there below the Access window. Print Preview.

There you go. That looks a lot better now, doesn't it? ID, then you have that stuff right there. I'm going to show you all kinds of cool tricks in future lessons. How to turn off these alternating bands, grouping levels, all kinds of neat stuff.

Let's close that.

That was kind of fun. Let's make another one. This is a single report where we've got a bunch of data for each customer, one customer at a time.

Click on Create and then Report Wizard. I like the Report Wizard. It's a good wizard. Pick the CustomerT again. You could pick a query, if you want just customers from New York or whatever. Whatever you want to appear in this report, you can set up a query for it and then base it on that.

But I want the CustomerT. It's got all my fields in it - LastName, CompanyName, City, State, Zip, Country, and let's add PhoneNumber and Notes. Just those fields.

Next. Do you want to add a grouping level? No. Next. What do you want it to look like? Sort by, let's go LastName and then FirstName. Next.

Let's do Columnar this time so we get the labels and the fields next to them. Next.

What name do you want? This will be my CustomerR, my customer report, and then preview. Hit Finish.

There we go. Now you can see I've got one customer - there is Joe Jones.

Scroll down. There's the next customer - Susan Jones. Page 1 of 8. Go to the next page and scroll back up here. There's the next customer.

Again, I do not like the CustomerID aligns to the right and everything else aligns to the left. Close the Print Preview. Click on CustomerID. Format. Align Left.

Again, I don't know why it keeps putting it down here. I'm going to draw a box that touches these, and then click and drag it up to the top. Then, click and drag a box that touches those and drag these down. That is how you can move these things around in Design View. We'll be spending a lot more time on this Design View in the next level, Level 2.

While we're in here, let's highlight these and bold them. Save it. Close it. Right-click. Print Preview.

Look at that. There we go. There is a single customer report. Scroll through. There's page 2, page 3. Notice my notes - they take a lot of space. Page 4.

People always ask me at this point: can you make it so you get one customer per page? Yes, absolutely you can. You can force a new page after the section. I'll cover that in a future class. That involves getting into some more advanced stuff, but it is possible.

Some other things you can also modify in here, once you're in Print Preview: go to Size under Page Size. Here is where you can change the type of paper that you're dealing with. Letter is the most popular, but if you're dealing with legal paper, there you go. That changes it to legal. Or A5, or an envelope, or whatever else you want to pick. I'm going to go back to letter.

You can change the margins if you want. There is normal, wide, narrow. If you go to wide, you can see what that looks like. You have more room around the page. You can set custom margins if you want to under Page Setup here. I'll talk about all of this in upcoming classes.

Here is where you can change from portrait to landscape. You can export reports in different formats. You can send them to Excel. You can make a text file. PDF is what most people do. Just click on the PDF or XPS. XPS is Microsoft's type of open document format. But everybody pretty much has PDF, so just pick PDF. Give it a file name. Pick where you want it to go. There is standard or minimum size. If you are sending it to someone by email, usually minimum size is fine. Standard if you are going to be actually printing it and you want high quality. I'm just going to hit Cancel.

You can also email it as an attachment. You can send it as a Word document. You can do all kinds of stuff up here.

Let's close this one. Let's make some mailing labels. The boss says, "I want to print out mailing labels for all of our active customers."

Not everybody. If you remember from our customer table, we have a field in here called IsActive. Some of these people are no longer active customers. Maybe they're deceased or they moved out of our area that we service. So I do not want to waste money mailing to those people.

What do I do? How do I make a report to print labels to just the active customers? Whenever you want to do any kind of criteria like that, what do we use? We use a query. Just like this Customer From New York. Remember this guy? We have a query down here, a criteria that says New York. So let's make a query that has only active customers in it.

Go to Create > Query Design. Go to Tables, bring in our CustomerT. I can close this now.

Bring in the fields that you want for your mailing labels. I don't care about CustomerID. I want FirstName, LastName, CompanyName, Address, City, State, Zip, Country, and then I need IsActive. For IsActive, I'm going to come down here in the criteria and I'm going to say True. That means only customers that are active will show up in this query. If I run this query now, look at that. They're all the active customers. I'm only seeing 13 of them, whereas if I open up the table, there are 16 total. So I have three inactive customers that will not show up in the mailing labels.

Do you have to sort this? No. We'll handle this sort again inside the report.

Save this. I'm going to call this CustomerActiveQ. I have a customer list. I like to keep all the customer stuff starting off with Customer. That way it's all grouped together. I try to keep my tables and queries in singular instead of saying CustomersActive. Later on, it just becomes less confusing. You don't have to say to yourself, "Was that CustomerActive or CustomersActive?"

Our query is all set. We have a query with just active customers. Now, we can use this to make our mailing label report. Go to Create > Labels.

Now, the wizard creates standard labels or custom sizes. First thing is to find the manufacturer of your favorite labels, whatever types of mailing labels you've got. I personally use Avery labels, and Avery is one of the most popular manufacturers. Even the third-party, the knockoffs, and the other brands, they usually have an Avery compatibility number.

I like 5160s. That's where you get 30 on a page. It's three columns, 10 rows - 30 per page. Of course, it might change English versus metric. Sheetfeeder = inkjet and laser printers. Continuous = dot-matrix printers where it just keeps feeding them in. Or you might have a continuous label printer, like a Dymo label printer. And yes, you can create custom labels. I do have lessons on that; that's a lot more advanced though. But find your label type that you want.

Hit Next.

Font, font size, font weight, text color. I'm going to stick with all the defaults. If you make your font size too big, you might get fewer lines on the labels. I don't think you can go 20 point in here and get five lines on there. Eight point is usually good enough. You do not want to go too small either, because sometimes the post office, you might not be able to read your label.

Next.

Now, we're going to set up our prototype label. Basically, we pick the fields over here and arrange them on this prototype label how we want them to appear on the label itself. Bring over FirstName - double-click, or you can click that button there. Now, between FirstName and LastName, what do I want? A space. Press the space key on your keyboard. Now bring over LastName.

Time for the next line. Enter. Press Enter on your keyboard. People always miss that step. Now, if CompanyName is blank, it will not put a blank line there. It will just squeeze out that empty space. It's kind of neat.

Enter. Address. Enter. Now, we have City, space. Sometimes people put a comma there - I don't. State, space, Zip, space, and then Country. If you have a larger label or you go with a small font, you could fit Country on the next line. But this usually works just fine. I leave United States. I'm in the United States, so I leave US customers blank, so it does not print a USA there. That's one of the reasons I do that.

Do I want IsActive on the label? No. I'll just leave it over here.

Next.

What fields would you like to sort by? If you're doing bulk mailing and you're printing thousands of these, and you have a bulk mail permit, you'll get a discount. You might want to sort by Zip code and then Address, so all your stuff is properly sorted as it's printed. I'm not going to bother with that. I'm going to sort it by LastName and then FirstName. This is where you control the sort. You don't have to do it in the query.

Next.

What name do you want? We do not want that. We want CustomerMailingLabelR. All singular. Again, that's my preference. You can name it whatever you want. No spaces - again, I emphasize no spaces in your object names. Trust me, if you do decide to get into development and programming, you will thank me for not having spaces. They cause so many problems.

Finish. Let's see the labels as they will look when they are printed. Finish.

This is a popular error message that comes up almost all the time. It says, "Some data may not be displayed. There is not enough horizontal space." The wizard sometimes makes the width of the label just a little bit too wide. It happens all the time. I get tons of emails from people about this. Just hit OK. It still looks fine, doesn't it? It looks just fine. But that error message still pops up.

Close this. If you go back into Print Preview, right-click Print Preview, you still get that. How do we get rid of that?

Go to Design View. Take this edge right here, this little tiny edge, and just click it and drag it in just a teeny tiny bit. Even that sometimes is enough. Save it. Let's go back into Print Preview again.

If not, just eat off just a little bit more. I think this is a long-time Access bug. Let's bring it in a tiny bit more, and maybe slide these over a little bit like that. They'll still fit just fine on the labels.

Right-click. Print Preview. There we go.

It gets rid of that error message, and these will still print just fine. There are your mailing labels. When you're ready to print, just come over here and click the Print button. I'm not going to do it because I do not want to waste a sheet of paper, but that's that.

Now, I have to give you my laser printer talk at this point. Notice how I've only got half a sheet of labels that are going to print. So I have a bunch down here, like four times three, twelve, thirteen. So I'm going to have, what, seventeen blank labels on the bottom down here.

If you have an inkjet printer or a dot-matrix printer, you can ignore this. If you're using a laser printer, a laser printer, you do not want to feed a sheet of labels through a laser printer more than once. Why? Because laser printers use heat when they print. They basically sprinkle on some toner, and then the heater bakes the toner onto the page. That heat tends to cause the glue behind those labels to soften. If you run the same sheet of labels through the laser printer and it keeps getting repeatedly heated, those labels have a tendency to peel off inside the printer and get stuck to the rollers. I have personally had this happen to me and a couple of my clients, and it's expensive to have your printer taken apart and have those labels peeled off. You are better off just buying a new printer. So if you have a laser printer, don't feed your sheets of labels through more than once.

If you print a lot of one-off, single labels, I do highly recommend the Dymo label printers. I have lessons on how to use those with Access. In fact, I have a free one on my website. I'll put a link down in the link section below on how to print to a Dymo label printer. They are pretty cool. These guys, these little printers right here. In this video, I'll show you how to use them.

Let's go ahead and close that. There we go.

Now we've got a couple of different reports in our system. We can also print or mail to people.

We covered a lot of stuff today. We learned how to build a table, how to build queries based on that table with different criteria, built a customer form to edit and enter data on the screen, and we've got a couple of different reports that we can print out.

Thank you.

Quiz

Q1. What is the main purpose of reports in Microsoft Access?
A. To enter and edit data in the database
B. To print or display data for users who do not use the database directly
C. To create tables for storing information
D. To write code for macros and modules

Q2. Which of the following is NOT a view available for reports in Access?
A. Table View
B. Report View
C. Print Preview
D. Design View

Q3. Why might you want to include the Date and Time on a report but not on a form?
A. Forms are always printed and need dates
B. Reports are designed to be shared or printed, making the generation date and time useful
C. Reports cannot display Date and Time
D. Forms cannot display any date or time information

Q4. What is the main advantage of using the Report Wizard in Access?
A. It automatically generates VBA code
B. It guides you through selecting fields, layout, sorting, and grouping, making report creation easier
C. It only creates mailing labels
D. It is the only way to create a report in Access

Q5. If you want to create a report showing only active customers, what should you do first?
A. Print the entire CustomerT table
B. Change the IsActive field on each report manually
C. Create a query that filters for only active customers
D. Delete inactive customers from the table

Q6. When designing a mailing label report, how can you ensure inactive customers are not included?
A. Exclude the IsActive field from the label prototype
B. Use a query with a criteria of IsActive set to True and base the report on that query
C. Sort the report by IsActive
D. Remove inactive labels after printing

Q7. Which of the following is true about mailing labels and laser printers?
A. It is fine to run the same sheet of labels through a laser printer multiple times
B. Heat from laser printers can cause labels to peel and potentially damage the printer if reused
C. You must always use wide margins when printing labels
D. Inkjet printers cannot print on mailing labels

Q8. What are the different sections of a report in Access Design View?
A. Only Report Header and Report Footer
B. Detail, Page Header, Page Footer, Report Header, and Report Footer
C. Table Header, Query Footer
D. Main Section and Sub Section

Q9. How can you adjust the alignment of text boxes or labels in a report?
A. They cannot be aligned in Access
B. By selecting them and choosing the desired alignment option under the Format tab
C. Only labels can be aligned, not text boxes
D. By opening the report in Print Preview and dragging them

Q10. What layout type would you select in the Report Wizard to show one record per row, similar to a spreadsheet?
A. Columnar
B. Tabular
C. Justified
D. Continuous

Q11. How do you eliminate the common error message "Some data may not be displayed. There is not enough horizontal space" when creating mailing labels?
A. Increase the width of the label section
B. Remove fields from the label design
C. Slightly decrease the width of the label in Design View
D. Ignore the message; it cannot be resolved

Q12. What is the benefit of exporting a report as a PDF in Access?
A. It allows others to edit the report data
B. It creates a high-quality, shareable, and printable document
C. It generates a PowerPoint presentation
D. It links directly to the database for live updates

Q13. Why might you want to use custom names without spaces (such as CustomerListR) for your Access objects?
A. It makes them easier to read
B. It helps avoid problems in VBA programming and when working with object references
C. Access does not allow spaces in object names
D. It is only required for reports

Q14. Which steps are necessary to sort mailing labels by LastName and then FirstName?
A. Set the sort order in the table before running the report
B. Add sorting in the report wizard during label report creation
C. Only sort in the query
D. Sorting is not possible on mailing labels

Q15. What is the effect of having a blank CompanyName field on a mailing label?
A. The label will not print
B. The label prototype will show an error
C. Access will automatically suppress the blank line, closing up the space
D. A blank line will always appear, wasting label space

Answers: 1-B; 2-A; 3-B; 4-B; 5-C; 6-B; 7-B; 8-B; 9-B; 10-B; 11-C; 12-B; 13-B; 14-B; 15-C

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 is all about creating customer reports in Microsoft Access. We'll be learning how to put together a few different types of customer reports, including a simple single-customer report, a customer list showing all your customers, and how to set up mailing labels that only get printed for active customers. Along the way, I'll cover important topics such as setting paper size, adjusting margins, and handling printing options.

Up to this point, we've learned how to make tables, create queries, and design forms. Reports are built very similarly to forms, but there's an important distinction: forms are primarily for users actively entering or editing data in your database, while reports are designed for presenting that data to others. You might print reports, send them as email attachments, or use them in any situation where someone needs to view data without interacting with the database itself.

The process of building a report starts by choosing a table or query as the data source. After selecting your data source, you move to the Create tab, where there are various options for making tables, queries, forms, and reports. Just like forms, Access offers several ways to build reports. There's the simple report designer, which automatically creates a basic layout based on your selected table or query. This approach is very quick, and Access does most of the work, though the results often require further improvement.

For more control, you can use Report Design, which gives you a blank canvas so you can start from scratch. This is useful for more advanced and customized reports and is something I'll introduce in future lessons. There's also the Blank Report option, which sets up a default layout that you can adjust right away in layout mode. On top of that, you have the Report Wizard, which I highly recommend. The Report Wizard gives you greater control over which fields to include, how to group and sort data, and the general look and feel of the report. I'll demonstrate the Report Wizard in this lesson. Finally, Access provides tools for creating labels, which we'll use for mailing labels later.

Let's start by creating a simple report. After selecting the customer table (CustomerT), you can have Access auto-generate a basic report. This will look very much like a continuous form, with all customer records listed down the page and columns for each field. The default layout includes a title at the top, which you can rename, and a date and time stamp. I typically don't use the date and time stamps on forms, but having them on reports is often helpful so you know when the report was generated. You can customize these details or remove them as needed.

Reports, like forms, have multiple views. Report View is what you'll see by default, but Print Preview is the most important since it shows exactly how your report will appear on paper. In Print Preview, you can navigate between pages, check the layout, and verify that everything looks correct before printing.

Most of the time, the automatically generated report isn't very well formatted, so it's common to close it without saving changes and opt for a better approach. Let's make a more focused customer list report using the Report Wizard. The Report Wizard is especially helpful because it lets you control which fields appear on the report, define grouping and sorting, and select the desired layout.

When using the Report Wizard, start by selecting the source table or query. I always recommend using a consistent naming convention, such as ending table names with T, queries with Q, and forms with F. This makes it much easier to tell at a glance what type of object you're working with, especially as your database grows and you later move into more advanced Access programming with macros and VBA.

For our customer list report, I'll choose CustomerT and pick just a few fields to display: CustomerID, FirstName, LastName, CompanyName, and PhoneNumber. You can easily include or exclude fields using the simple interface. Next, the wizard asks if you'd like to group records. Grouping by a field such as State is an option, but we'll keep it simple for now.

You can also set up sorting. For this example, I'll sort by LastName, then by FirstName. This sort order takes precedence over any sort you may have set up in the underlying query when the report is based directly on a table.

Next, you'll pick a layout. The Tabular layout arranges each record on a single row (similar to a spreadsheet), while the Columnar layout displays each record more like a form with labels and values side by side. There is also a Justified layout that can resemble an invoice. For our list, Tabular layout makes the most sense.

Choose Landscape orientation to fit more columns on one page, and let Access automatically adjust the field widths so everything fits neatly. Give your report a name, such as CustomerListR, and opt to preview it when finished.

The Report Wizard produces a nicely formatted report with your selected fields arranged in the order you specified. You may notice that fields can still be rearranged after the report is created. For example, if you want CustomerID to appear before LastName, you can switch views to Design View and simply drag the controls where you want them. In the Design View, reports are organized into sections: Detail for the individual records, Page Header for the top of each printed page, Report Header for the top of the entire report, and corresponding footers at the bottom. As you gain experience, you'll spend more time making adjustments in Design View, but for now, this is just a preview of what's possible.

After making any needed layout adjustments, be sure to save your report, close it, and then use Print Preview to check your changes. I like to make sure that data fields, especially numbers like CustomerID, are left-aligned for consistency and clarity.

Next, let's build a single-customer report presenting more detailed information about one customer at a time. Again, use the Report Wizard and select CustomerT. Choose the fields you want, such as LastName, CompanyName, City, State, Zip, Country, PhoneNumber, and Notes. After setting any sort order (say, by LastName and then FirstName), select the Columnar layout for a more form-like display. Name the report CustomerR and preview it.

This produces a report where each customer takes up an entire segment of the page, perfect for summary printouts or one-page reports. You can make the same formatting changes as before, adjusting field alignment and moving labels as desired. Bold the labels for emphasis if you'd like. If you ever need each customer's information to start on a new page, there are settings available for that as well, which I'll cover in more detail in advanced lessons.

Within Print Preview, Access provides options to control page size, choose different types of paper (like Letter, Legal, envelopes, etc.), tweak margins, and switch between portrait and landscape orientation. You also have numerous export options to create PDF files, Excel sheets, or Word documents. PDFs are the most commonly used, especially for sharing or printing finalized reports. You can choose between standard and smaller PDF file sizes depending on your needs.

Now, let's talk about mailing labels. Suppose you want to generate mailing labels only for active customers. In that case, you'll need to create a query that includes only those customers. Use Query Design to build a query based on CustomerT, bringing in the appropriate fields for the mailing label such as FirstName, LastName, CompanyName, Address, City, State, Zip, Country, and IsActive. Enter the criteria 'True' under the IsActive field to ensure only active customers are selected.

After running the query, you'll see only the active customers. Save this query as CustomerActiveQ for easy identification and to keep your naming consistent.

Now that you have a query for active customers, you can create your mailing labels. Use the Labels Wizard, select CustomerActiveQ as the data source, and choose your label manufacturer and type. Avery 5160 is a commonly used label type, which fits 30 labels on a standard sheet (three columns, ten rows). Consider whether you're using laser, inkjet, or continuous feed printers, and set your font and size appropriately for readability.

Arrange the fields on the label layout in the order you prefer. For example, start with FirstName and LastName on the first line, followed by CompanyName (if any), street Address, and then City, State, and Zip. Add Country on the same line or a subsequent line if needed. Access will skip blank fields, so empty data does not leave awkward gaps.

Set your sorting preference, such as LastName and FirstName, although for large bulk mailings you might opt to sort by Zip code instead. Name this label report CustomerMailingLabelR.

It's common to see an error at this point, warning that some data might not display due to insufficient horizontal space. This usually occurs because Access makes the label slightly too wide. To fix this, switch to Design View, adjust the label width by dragging the right edge in a small amount, and save. After narrowing the width just a bit, the error message will disappear when you return to Print Preview, and your labels will print correctly.

Here's a tip: if you use a laser printer, avoid running the same sheet of labels through the printer more than once. The heat from the printer can loosen the adhesive on previously printed labels, which may then peel off and stick to the printer rollers. Repeated use can damage your printer. For printing individual or small batches of labels, specialized label printers like the Dymo models are excellent and avoid this issue completely.

With these reports and mailing labels in place, your system now not only records customer data, but also allows you to present and distribute that information in a polished, professional way.

We accomplished quite a lot in this lesson. You now know how to build tables, create queries with specific criteria, set up forms for viewing and editing, and use reports for presenting and sharing data. If you want detailed, step-by-step visual instructions, I have a complete video tutorial available on my website at the link below. Live long and prosper, my friends.

Topic List

Difference between forms and reports in Access
Creating a simple customer report
Using Report View, Print Preview, Layout View, and Design View
Editing report layout and titles
Deleting and modifying report headers and footers
Using Report Wizard to create custom reports
Selecting specific fields for reports
Sorting and grouping data in reports
Choosing report layouts: Tabular and Columnar
Moving and aligning fields in Design View
Changing label text and field alignment
Saving and previewing reports
Exporting reports as PDF or other formats
Printing reports and setting page orientation
Creating a query to filter active customers
Building mailing label reports for active customers only
Using the Label Wizard for mailing labels
Arranging fields and formatting labels
Fixing label width error in mailing labels
Best practices for printing labels on laser printers

Article

Reports in Microsoft Access provide a way to present information from your database to others in a professional, printable format. Unlike forms, which are used for entering, editing, and viewing data directly in the database, reports are designed for output: you can print them, export to PDF, send via email, or simply share the information in a readable way. Reports are especially useful when you want to display data in a more organized, formatted, and attractive way than what is possible with tables or simple queries.

To create a report, you first need to decide which data you want to display. This data typically comes from either a table or a query. In Access, there are several ways to create a report: you can use the Report button for a quick layout, use the Report Wizard for more control, build from scratch with the blank report option, or use the label report tool for things like mailing labels.

Let me walk you through making a simple customer list report. Imagine you have a table called CustomerT that contains fields like CustomerID, FirstName, LastName, CompanyName, and PhoneNumber. To start, select CustomerT and click on the Create tab, then choose the Report button. Access instantly generates a basic report for you, which you can view, scroll through to see all your records, and even print if you wish. The title at the top can be renamed by double-clicking on it, and you can remove the date and time if they are not needed.

Reports in Access have different views. Report View lets you see the data on-screen, while Print Preview shows you exactly what the printed report will look like. Use the navigation buttons at the bottom to move between pages. While the simple report is quick, it usually needs improvement in layout and formatting. Layout View allows you to resize columns and rows easily; Design View gives you more power over adding, moving, and formatting fields and labels.

For more control over your report, try using the Report Wizard. This wizard guides you step-by-step through choosing which table or query to base the report on, selecting which fields to include, and deciding on grouping and sorting. For example, to make a customer list, you could select CustomerID, FirstName, LastName, CompanyName, and PhoneNumber. You can decide how to sort the data, such as by LastName and then FirstName. Choose the Tabular layout for a spreadsheet-style list, and select Landscape orientation if you have many fields. After finishing, review the report in Print Preview.

If you want to adjust the layout, such as moving the CustomerID field to appear before LastName, switch to Design View. Here, you can click and drag the fields and their corresponding labels to arrange them as you like. Design View also reveals sections like Report Header, Page Header, Details, Page Footer, and Report Footer. For instance, the title and column headings are in the header sections, while the actual records appear in the Details section. Once finished rearranging, right-click the report in the navigation pane and choose Print Preview to check your changes.

If a field like CustomerID is too wide or not aligned how you want, you can adjust its size and alignment in Design View. To tidy up, shorten the label to say ID instead of CustomerID and align both the label and the textbox to the left for consistency with the rest of your data.

For a report showing one customer at a time with detailed information, use the Report Wizard and choose the Columnar layout instead of Tabular. Select the fields you want, such as LastName, CompanyName, City, State, Zip, Country, PhoneNumber, and Notes. After finishing, you will see each customer on a separate page with their details lined up vertically. To force each customer to start on a new page, you can set up page breaks, but that is a more advanced feature covered elsewhere.

When working in Print Preview, you can adjust paper size, margins, and orientation under the Page Size and Page Setup tabs. Common paper sizes like Letter are selected by default, but if needed, you can use Legal, A4, envelopes, or other types. Standard margins are usually sufficient, but you can make them wider or narrower, or set custom margins as needed. Reports can be exported to various formats, with PDF being the most popular for sharing and printing.

Mailing labels are another practical report type you might need. Suppose you want to create mailing labels only for active customers. To do this, create a query that filters customers where the IsActive field is True. In Query Design, select your CustomerT table, bring in fields like FirstName, LastName, CompanyName, Address, City, State, Zip, and Country, and add the IsActive field with True as the criteria. Save this as CustomerActiveQ. This query now returns just the active customers.

With the active customers query ready, go to the Create tab and choose Labels. The label wizard will help you pick the label type based on the manufacturer and label number, such as Avery 5160 for standard 30-per-sheet labels. Customize the font and label layout as you wish. Use the fields to arrange the address on the label: combine FirstName and LastName on the first line, add CompanyName if desired, then Address, and City, State, and Zip on the next lines. When a field like CompanyName is blank, Access automatically removes the empty line.

After arranging the label's layout, decide how to sort the records, for instance by LastName and then FirstName. Name your report CustomerMailingLabelR and finish. Sometimes you may see an error saying not enough horizontal space; this is a common quirk with the wizard. To fix it, simply go to Design View and slightly decrease the width of the labels by dragging the right edge inward, then go back to Print Preview to verify the change and clear the error.

Be careful with printing on partially used sheets of labels, especially with laser printers. The heat from a laser printer can cause label glue to loosen, which over repeated passes can result in labels peeling off inside the printer. This can cause jams and damage, so it is best not to reuse label sheets in a laser printer. For single label printing, a specialized label printer like a Dymo is a better option.

With these steps, you now know how to create simple reports, custom list reports, single record reports, and mailing labels in Access. Reports allow you to present your data clearly and professionally, whether for internal reviews, customer mailings, or sharing information outside your database. As you become more comfortable, you will discover more ways to customize and enhance your reports to fit your needs.