How to create a report from a query in Access

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

Introduction

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:

//community.tableau.com/docs/DOC-1236

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

SELECT TBLORDERS.ORDER_MONTH,

DATEADD(‘M’, -1, TBLORDERS.ORDER_MONTH) AS ORDER_PREV_MONTH,

TBLORDERS.REGION,

SUM(TBLORDERS.PROFIT)                   AS SUM_PROFIT,

SUM(TBLORDERS.SALES)                    AS SUM_SALES,

AVG(TBLORDERS.PROFIT_PERCENT)           AS AVG_PROFIT_PERCENT

FROM   TBLORDERS

GROUP  BY TBLORDERS.ORDER_MONTH,

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 //www.w3schools.com/sql/. //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

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.ORDER_PREV_MONTH,

qryOrdersRegionReportBase.REGION,

qryOrdersRegionReportBase.SUM_PROFIT,

qryOrdersRegionReportBase.SUM_SALES,

qryOrdersRegionReportBase.AVG_PROFIT_PERCENT,

qryOrdersRegionReportBase_1.SUM_PROFIT AS SUM_PROFIT_PREV,

qryOrdersRegionReportBase_1.SUM_SALES AS SUM_SALES_PREV,

qryOrdersRegionReportBase_1.AVG_PROFIT_PERCENT AS AVG_PROFIT_PERCENT_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

IIF(ISNULL(SUM_PROFIT_PREV),

NULL,

(SUM_PROFIT – SUM_PROFIT_PREV) / SUM_PROFIT_PREV) AS SUM_PROFIT_VAR

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

IIF(ISNULL(SUM_SALES_PREV),

NULL,

(SUM_SALES – SUM_SALES_PREV) / SUM_SALES_PREV) AS SUM_SALES_VAR,

IIF(ISNULL(AVG_PROFT_PERCENT_PREV),

NULL,

(AVG_PROFIT_PERCENT – AVG_PROFIT_PERCENT_PREV) / AVG_PROFIT_PERCENT_PREV) AS AVG_PROFIT_PERCENT_VAR

The final query should look like

SELECT qryOrdersRegionReportBasePrev.ORDER_MONTH,

qryOrdersRegionReportBasePrev.ORDER_PREV_MONTH,

qryOrdersRegionReportBasePrev.REGION,

qryOrdersRegionReportBasePrev.SUM_PROFIT,

qryOrdersRegionReportBasePrev.SUM_SALES,

qryOrdersRegionReportBasePrev.AVG_PROFIT_PERCENT,

qryOrdersRegionReportBasePrev.SUM_PROFIT_PREV,

qryOrdersRegionReportBasePrev.SUM_SALES_PREV,

qryOrdersRegionReportBasePrev.AVG_PROFIT_PERCENT_PREV,

IIF(ISNULL(SUM_PROFIT_PREV) OR SUM_PROFIT_PREV = 0, NULL, (SUM_PROFIT – SUM_PROFIT_PREV) / SUM_PROFIT_PREV) AS SUM_PROFIT_VAR,

IIF(ISNULL(SUM_SALES_PREV) OR SUM_SALES_PREV = 0, NULL, (SUM_SALES – SUM_SALES_PREV) / SUM_SALES_PREV) AS SUM_SALES_VAR,

IIF(ISNULL(AVG_PROFIT_PERCENT_PREV) OR AVG_PROFIT_PERCENT_PREV = 0, NULL, (AVG_PROFIT_PERCENT – AVG_PROFIT_PERCENT_PREV) / AVG_PROFIT_PERCENT_PREV) AS AVG_PROFIT_PERCENT_VAR

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

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

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

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

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.

//www.mydatacareer.com/how-to-import-excel-into-access/

How do I turn a query into a report in Access?

Can a report be created from a query?

How do you link a query to a report?