how to create a report in access

How To Create A Report in Access Using SQL Query

Microsoft Access allows the creation of SQL Queries visually but complex reports require SQL knowledge. 


In this article we will explain how to create a report in access using SQL query.  In Microsoft Access, it is possible to accomplish most things in at least a couple of different ways, and creating a Report is no different. You can also build a professional report in Access using the graphical interface.

In any case, let us turn to creating a report in Access using SQL.Specifically, we will create a sales Report based on some dummy sample sales data.

The data used to create this report in Access was obtained here:


Some columns were removed from the data-set, and the actual sample data was added as part of this tutorial do be downloaded as an Excel file.

The First Steps

The first mandatory step is to put data into a blank Microsoft Access database. We will use a much simpler approach to import data than the one explained in the last tutorial, simply a copy and paste operation.

The reader should have a blank Access database created and, after that, open the Microsoft Excel provided file. Both can be opened side by side as illustrated below

Now the reader should select all rows in the Excel file, including the header row, right click on the mouse and from the context menu select Copy

Now inside the Access file, on the left pane, right click and from the context menu select paste

The reader should answer yes when prompted regarding column headings

After importing data rename the table to tblOrders as shown in the next pictures

Now the base report data is imported, let’s start building the report.

The base idea for this report is to show the Sales Total Value, the Sales Total Profit and the contiguous variations per sales region and month.

The first required operation is to aggregate base data accordingly. Data is to be shown by region so if the user pays attention to the original data in the picture bellow it is easy to understand its granularity is not the required one. The process of transforming data will require several operations, it is a divide and conquer process that will break the entire data calculations into small steps.

First let’s create an initial query that will aggregate data by order month (pay attention to the fact the order month column is a date, all have the first day of month). Using date data type will allow running date operations against data, the format can later be changed for presentation proposes.

Considering tblOrders as initial data source the user should create an initial query having the following SQL code









Microsoft Access allows the creation of SQL Queries visually but complex reports require usually SQL knowledge so we will explain the base logic of the created queries.

As a side note, SQL language is present in all relational databases and there are several free tutorials available in web. We strongly advise the reader to consider reading one if not familiar to SQL yet. One good tutorial can be found here https://www.w3schools.com/sql/. https://www.w3schools.com/sql/func_dateadd.asp

Regarding above query

  1. We are pulling data from the tblOrders table SELECT …. FROM tblOrders. Then the SQL statement is applying the grouping functions SUM to the fields PROFIT and SALES and the grouping function AVG to the field PROFIT_PERCENT.


  1. There are two original fields remaining ORDER_MONTH and REGION and a new calculated field called ORDER_PREV_MONTH (will be used later to compute variations among months). Once no grouping function is applied to these fields they should be added to the GROUP BY clause in the end. The SQL engine will provide the two sums and the average per each existing combination of the remaining three fields.

To create the query go to the tab Create and select Query Design



After that close the table selection window

Right click and select SQL View or select it from the top left corner

Now copy the previous SQL statement and paste it into the blank white area (replacing any SQL present there) and execute it by pressing the run button on top

The outcome should be the following

Click the save button on the top left corner and name the query qryOrdersRegionReportBase

Now let’s build a second query using the qryOrdersRegionReportBase as source (remember the divide and conquer logic). In the previous query we calculated a new field based in the ORDER_MONTH field. That field is simply the ORDER_MONTH minus one so we can have for the same line the actual ORDER_MONTH and the previous one. In this step we will use the qryOrdersRegionReportBase twice in the same query using it as driving table and a second reference to it to pull data for the previous month. We will build a left outer join query. We will build this one graphically so the reader can also understand how the visual editor works.

Once again click in Create -> Query design and from the bellow window, tab Queries, select the previously created query by clicking add twice and two instances are present in the pane

The screen should look like the picture below.

Next we need to join the queries and pull the required fields. The base idea here is to pull the original query fields and put the previous month metrics in the same row. Once we have the actual month and previous month metrics in the same row we will be able to compute relative variations to present in the report.

To join both queries select the field ORDER_PREV_MONTH (from the left query) and drag it to the right query to field ORDER_MONTH. After that select the field Region (from the left query) and drag it to the Region field on the right query. In the end both queries should be joined as follows

qryOrdersRegionReportBase   qryOrdersRegionReportBase_1
Region = Region


The problem is the default table / query join type only returns rows that match among both selected queries, also called an inner join. This could be a problem as the first series month will not have previous month data so it would be removed. To solve this we must change the join type to an outer join. We must “explain” the SQL engine that all rows present in qryOrdersRegionReportBase should be returned and, if there is any data from the previous month, it should pull it as well otherwise just let the fields be null.

So from the below image

Double click on each link line and from the prompted window

Select the second option that forces all rows from the left query to appear. Repeat the same logic for the Region field (bellow line in the join image). In the end both linking lines should show arrows pointing from left to right.

Now the join conditions are properly defined, we can select the required outcome fields. From the left query select all fields and drag them down to the columns area below

Repeat the same operation for the right query but only drag the last three fields. We will need to provide them an alias once they have the same name the current month ones have. The final field set should look like

Let’s provide the following field alias for the right query pulled fields. The logic will be to add the PREV suffix to the original name. Let’s do it using SQL, right click on the grey area and change to SQL view (this can also be made in the design view directly in the field name replacing for instance SUM_PROFIT by SUM_PROFIT_PREV : SUM_PROFIT)

When changing to SQL view the underlying (formatted) code should be the following

SELECT qryOrdersRegionReportBase.ORDER_MONTH,






qryOrdersRegionReportBase_1.SUM_PROFIT AS SUM_PROFIT_PREV,

qryOrdersRegionReportBase_1.SUM_SALES AS SUM_SALES_PREV,


FROM qryOrdersRegionReportBase

LEFT JOIN qryOrdersRegionReportBase AS qryOrdersRegionReportBase_1

ON (qryOrdersRegionReportBase.REGION = qryOrdersRegionReportBase_1.REGION) AND

(qryOrdersRegionReportBase.ORDER_PREV_MONTH = qryOrdersRegionReportBase_1.ORDER_MONTH);

To change the return field name just add the AS word after each original name and after that the desired alias (complex names and names with spaces should be enclosed by square brackets). Hit the run button and the final output should be the following

Pay attention to the selected lines, they relate to the first order month and you do not have data for the previous month.

Let’s save the query by clicking in the top left corner button and name it qryOrdersRegionReportBasePrev.

Now, let’s continue our divide and conquer approach and let’s compute the relative variations for the three metric fields between previous month and actual lines’ month. This should be simpler if we use SQL.

Let’s pick the actual query and add three extra calculated fields. Pay attention to the fact these will not be grouping functions they will be new calculated fields at row level, each new field will be a combination of already existing fields. Each new field will be obtained by testing if the previous data exists and if so a relative variation can be computed so for SUM_PROFIT the relative variation can be computed by




The underlying logic is if the previous month value does not exist or is 0 (0 cannot be present in denominator) then NULL is returned else it will compute the relative variation for SUM_PROFIT values.

A similar logic can be applied to SUM_SALES and AVG_PROFIT_PERCENT. The obtained computed fields are obtained as







The final query should look like

SELECT qryOrdersRegionReportBasePrev.ORDER_MONTH,












FROM qryOrdersRegionReportBasePrev;

Just create a new query and copy and paste the above SQL, save and name it qryOrdersRegionReportFinal. The following image shows what is expected so far

Now let’s build the report. The fast approach to build a report is by using the Microsoft Access report wizard. All operations can be made manually but it is faster to use the wizard and then make the desired changes if any is required.

From the top ribbon, create tab, click the report wizard button as shown in the picture below

Select qryOrdersRegionReportFinal as report main data source

Select the fields as presented bellow (using the single arrow or by double clicking) and hit next

Let’s add a grouping level as the information should be grouped by Region, press next

Data should also be ordered within the group from the most recent month to the oldest one so, in the next window, let’s add the ordering by ORDER_MONTH descending. Hit next again and we can use the default values present in the layout window

Press next again and name the report repOrdersRegionReport. Select Preview the report and click finish

How To Create A Report in Access

How To Create A Report in Access

The expected outcome should be similar to the following image. As the reader may notice the wizard has limitations and several layout improvements are required yet. The first thing the reader should pay attention to is the ORDER_MONTH field is ordered descending as required.

Let’s put it pretty. First right click on it and select design mode from the context menu so it is possible to access all report controls.

Let’s change the top header label caption and report caption to Orders Region Report

After that let’s change the format for ORDER_MONTH text field (in the detail area) to mmm/yyyy, then SUM_PROFIT and SUM_SALES to Currency with two decimal places (both can be select using control key and mouse)

In the next formatting change the SUM_PROFIT_VAR, SUM_SALES_VAR and AVG_PROFIT_PERCENT_VAR format to Percent with two decimals

Finally change the page header labels’ descriptions as shown bellow. To see the report just select preview the report from the top left button and if required adjust the margins or report layout from the Page Setup tab.

The report should look like

How To Create A Report in Access

How To Create A Report in Access

Now let’s add a conditional formatting so negative variations can be marked in yellow and the report can be more informative. Once again put the report in design mode and multi-select the variation detail fields as presented below

Once selected, click the Conditional Formatting button and the Conditional Formatting window opens. Remember we will be adding conditional formatting to all selected controls

How To Create A Report in Access

How To Create A Report in Access

Click New Rule

Choose the first option from the top list and put all values as follows in the next image

The rule being implemented will apply the defined format if the value present in the text box is less than zero. The format can be any we will only change the text box color to yellow. Select the color, click ok and the format is set. Close the previous window.

Go to the design tab and change the report view to print preview, the final layout should be like this

How To Create A Report in Access

How To Create A Report in Access


Believe it or not, our how to create a report in access using SQL tutorial just scratches the surface. You can use access to produce even more highly complex reports, mixing data from several data sources, receiving parameters from forms or using complex control formulas.

Similar to Access forms, it is also possible to add sub-reports to reports and quite elaborated dashboards can be produced. Using VBA, it is even possible to manipulate report controls in run time, moving them, changing them, hiding them.

Based on the high number of reports I have developed there were very few situations where Microsoft Access was not able to produce the desired outcomes and it presents itself as a very cheap solution when compared to highly expensive Business Intelligence tools.

How To Import Excel Into Access using VBA; step by step


3 Responses

  1. Hi, It is a useful article. I would like to ask a question here, how can we prepare same independent report through VBA, all the data retrieving, manipulation and calculations in VBA through recordset(s), no query object involved from outside this report object?

    Your expert guidance is requested.

    • The recordset is just a structure to store a query outcome or table data.
      The recordsets in VBA, c#, Java (resultsets) etc. have a lot of methods and
      properties available.

      Of course it is possible to mimic SQL logic by looping through all or some
      of the records present in a recordset.
      The same outcome can be acchieved if one uses arrays, collections or other
      data structures.
      Either way let me tell you, to mimic some SQL operations one needs to write
      at least 10 times more and less efficient code so why re-invent the wheel?

      Also, Access reports are built assuming an underlying query or table exists
      to provide data.
      Their grouping and section logic expects it.

      One should use the correct tools for the jobs and to group data the best is
      indeed SQL language.
      In the end you will always need to have a recordset that stores the base
      data for the report and if one does not define the base SQL for the report
      at least one should assign the report recordset property to the one that
      was created programatically.

      But the logic Access uses is: use SQL the most you can …

      • The methods in recordsets include moving next record, move to last, to
        first, back, etc.
        One can access each recordset field value.
        For instance to get a running sum one would need to use an auxiliary
        variable and increment it row by row, you see the picture.

Leave a Reply

Your email address will not be published. Required fields are marked *