Building Reports in Microsoft Access That Are Worth Sending

A report is how a database talks to people who never open it — the invoice a customer receives, the summary a manager reads on a Monday, the sheet that goes to the accountant. It is usually the only part of the system anyone outside the office sees, and it is the part most often left looking like a printed spreadsheet.

This is a practical guide to building Access reports that are worth sending.

Build the query first

The most useful rule here: a report should almost never be based directly on a table.

Build a query that returns exactly the rows and columns the report needs, in roughly the right order, and base the report on that. It keeps the filtering in one place, it lets you test what the report will show before designing anything, and it means changing what is included later is a query edit rather than a redesign.

It also avoids the commonest cause of a wrong report: an inner join that quietly drops records. "Every customer and what they spent" built on a plain join omits every customer who spent nothing — often exactly the ones the report was requested for. Check the row count in the query before you go near the report designer.

The fastest route to something usable

Select your query, then Create → Report. Access lays out every field with headings and a total. It will not be pretty, but it proves the data is right.

Create → Report Wizard is the better starting point for anything real, because it asks the questions that actually shape a report — which fields, how to group, how to sort, what to total. Answer those and you get a working structure to refine.

Then use Layout View to adjust it. It shows real data while you resize and move things, which Design View does not, and that difference saves a great deal of guesswork about column widths.

The five bands, and what belongs in each

Every Access report is made of horizontal bands, and understanding them is most of the skill:

• Report Header — prints once at the very start. Title, logo, the date range covered.
• Page Header — prints at the top of every page. Column headings belong here, so they repeat when the report runs to several pages.
• Detail — prints once per record. Keep it tight; every millimetre here is multiplied by the number of rows.
• Page Footer — every page. Page numbers.
• Report Footer — once at the end. Grand totals.

Getting a total in the wrong band is the classic mistake: a sum placed in the Page Footer totals the page, not the report. It looks right on page one and is wrong from page two onwards.

Grouping, which is where reports earn their keep

Open Group, Sort, and Total (on the Design tab). Grouping by customer, month or region turns a flat list into something someone can read, and adds a header and footer band per group so you can subtotal within it.

Three settings in that pane are worth knowing, because they fix the problems people usually live with:

• "with A on top" — sort direction per group. Sort a sales report by value descending and the interesting rows are at the top instead of alphabetically buried.
• "keep whole group together on one page" — stops a group's heading printing at the bottom of a page with its rows overleaf.
• "with a footer section" — this is what gives you subtotals per group.

For a subtotal, put a text box in the group footer with =Sum([Amount]). The same expression in the Report Footer gives the grand total. Access works out the scope from the band it sits in.

Expressions worth memorising

• =Sum([Amount]), =Count(*), =Avg([Amount]) — totals, scoped by band.
• ="Page " & [Page] & " of " & [Pages] — page numbering that tells the reader whether anything is missing.
• =Date() — when it was run, which every printed report should state.
• =[FirstName] & " " & [LastName] — joining fields.
• =Nz([Amount],0) — treat empty as zero. Without this, one empty value makes an entire calculated column blank rather than wrong-by-a-bit, which is at least obvious, but still needs fixing.

Set the Running Sum property on a text box to "Over Group" or "Over All" for a cumulative column — a running balance, or year-to-date. It is a property, not an expression, which is why people write far more complicated things to achieve it.

Conditional formatting

Select a control, then Format → Conditional Formatting. Overdue dates in red, negative margins highlighted, values above target in bold.

Used sparingly this is the single best thing you can do to make a report readable, because it puts the reader's eye where it needs to go. Used heavily it becomes decoration nobody reads. Two rules is usually enough.

Getting it out of Access

Reports rarely stay in Access. The two that matter:

• Export → PDF preserves the layout exactly. This is what you send to a customer.
• Export → Excel discards most formatting and grouping. Fine for data, wrong for a formatted report — if someone wants to work with the figures, give them the query output rather than the report.

To email a report on a button press, use the EMailDatabaseObject macro action with the output format set to PDF. That covers most "can this go out automatically" requests without any code.

What goes wrong

• Totals in the wrong band — correct on page one, wrong thereafter.
• Column headings in the Report Header instead of the Page Header, so they vanish after page one.
• Report built on a table, so filtering lives inside the report where nobody can find it.
• Designed on screen, never printed. Print one before releasing it. Column widths that fit the screen routinely spill onto a second page.
• No date and no page numbers, so a printout on someone's desk cannot be trusted or checked for completeness.
• An inner join hiding records, which is the failure nobody notices because the report looks complete.

When reporting outgrows Access

Access reports are capable, and for a printed document they are still perfectly good. The limits show up elsewhere:

• Nobody can see them without Access installed. There is no browser version — Access Web Apps were discontinued in 2018.
• They cannot be scheduled on their own; something has to open Access to produce them.
• They are static. There is no drilling into a figure to see what it is made of.
• If the numbers come from more than one system, they have to be combined by hand first.

If the requests you are getting are "can I see this on my phone", "can it arrive every Monday without anyone doing anything", or "can I click that total to see what is in it", those are not report design problems. That is the point where reporting moves onto a web application or a proper reporting tool over a server database.

The work is not lost, though. A well-built report is a specification: someone has already decided what the figures are, how they are grouped, and what counts as included. That is the part that takes the arguing.

Our Microsoft Access services page covers both improving what you have and moving beyond it, and a free system review will tell you which one you need.