Streamline reporting on shared business data by using queries and macros

Access can be very helpful when you need to tame a huge stream of data. Using Access, you can make the data easier to analyze, store, and summarize in reports. You can also use queries and macros to automate parts of the process, saving you time and helping prevent errors when it’s time for another set of reports.

This article uses a sample data set to demonstrate how you might create a database using shared business data, automate importing the data, and create and distribute reports to folks who need a summary understanding of the data.

In this article


Download sample data from the Download Center

To enable you to follow along as we explore this scenario, we’ve provided the sample data that was used to create this article, as well as a database file complete with all the data imported. The completed database contains all the queries, macros, and reports that this article explains how to create.

These resources are offered as an installable package – all you have to do is download the package and run it. When you go to the download page, click the Download button to get started. You can run the installer right away, or save it and then install when you are ready.

 Note    The package requires that you have write permission on drive C: in order to install correctly.

Download and install the sample resources from the Microsoft Download Center.

 Note    The sample database also includes several forms and associated queries that further automate the database. These objects are not discussed in this article. For links to more information about forms, see the section Explore ways to get more out of Access.

Prepare to follow along

After you install the package, follow these steps to finish preparing:

  1. Move the file named January2010Sales.txt from C:\MonthlySalesReport\MonthlyData\ to C:\MonthlySalesReport\, and then rename the file MonthlySalesData.txt.
  2. Open Access. On the File tab, click New, and then under Available Templates, click Blank database.
  3. On the right side of the screen, type MyMonthlySalesReports in the File Name box, and then click Create.
  4. Access creates the new database. Depending on your setup, one of the following may occur:
  • Access might open a new, empty table. If so, close the new table, but leave the new database open.
  • You might see a yellow bar just under the ribbon with a button labeled Enable Content. If so, click Enable Content.
  1. Leave the new database open. You’re now ready to follow along.

Top of Page Top of Page

Get external data into Access

There are two ways to get external data: linking and importing.

  • Linking is creating a connection so that Access treats the external data as if it were an Access table.
  • Importing is bringing external data into Access but not creating a connection. The imported data becomes an Access table.

This article explains how to import external data from a text file.

When you finish importing external data, you can save the operation as an import specification that you can use again to repeat the process exactly. You can save a specification regardless whether you link or import.

Import the source data

You use commands on the External Data tab to import source data.

Access Ribbon Image

The following procedure shows you how, using the sample data as prepared in the preceding section.

  1. On the External Data tab, in the Import & Link group, click Text File. The Get External Data – Text File dialog box opens.
  2. In the dialog box, click Browse, and then browse to the C:\MonthlySalesData folder.
  3. Double-click the MonthlySalesData.txt file.
  4. Click Import the source data into a new table in the current database, and then click OK. The Import Text Wizard starts.
  5. On the first screen of the wizard, click Delimited, and then click Next.

 Note    Not all text data is delimited. The sample data was prepared as using comma-separated values, a common convention for managing data extracts from big data sources. Other text files might be delimited using other characters, such as semi-colons, or they may be fixed-width – that is, data where each value uses the same number of characters.

  1. On the next screen, select the First Row Contains Field Names check box, and then click Next.
  2. On the next screen, click Next.

 Note    In most cases, you should use this screen of the wizard to review the fields in the text file that you are importing. To review a field, click the field on the wizard screen, and then review the options shown in the Field Options area. The sample data has been prepared so that this step is not necessary.

  1. On the next screen, click Next.

 Note    It’s usually a good idea to let Access create a primary key when you import data into a new table. However, the data you import may already have a field that is well-suited to use as the primary key.

  1. On the final screen of the wizard, type tblImport in the Import to Table box, and then click Finish. Access imports the data, and then opens the Get External Data – Text File dialog box. Leave the dialog box open – you will complete the next set of steps in the following section, Save the import specification.

 Note    You might not want to save the steps for some import operations. Saving the import steps is a good idea if you plan to import from the same data source on a regular basis – as is the case for this article and sample data set.

For an overview of using external data, see the article Introduction to importing and exporting data and the article Video: Import data to an Access database.

Save the import specification

  1. In the Get External Data – Text File dialog box, select the Save import steps check box. A new set of options appears.
  2. In the dialog box, describe the purpose of the import by typing in the Description box. This information will be stored with the specification, making it easier for someone else to understand what it is for.
  3. Click Save Import.

 Note    In some cases, you might want to create an Outlook Task to remind you when the next import is due, and to provide an easy way to run the import from Outlook. The sample data in this case needs to be manually copied to the correct location before the import can occur, and an Outlook Task would not save any time, so we won’t use this option. If you want to create an Outlook Task for a saved import specification, select the Create Outlook Task check box before you click Save Import.

For more information about saving an import specification, see the article Video: Save the details of an import or export operation

Now that you’ve imported the first text file, it’s time to normalize the data.

Top of Page Top of Page

Normalize your data by using the Table Analyzer

One of the main reasons to use Access is its support for relational data – data that is separated into discrete tables that are related to each other by common values.

For example, one table might be about web pages and another table might be about monthly views of those pages. Each row in the page views table could be related to a row in the web pages table. If you stored all the data in one table, each row would have to contain all the information you need to know about the web page, instead of that information being stored once and represented in the page views table by a single value.

Key fields

You connect normalized tables by using key fields. Each connection is known as a relationship. There are two main kinds of key:

  • A primary key is used to identify each record in a table. Every record in a table has a different value for the primary key. Primary keys often take the form of an incremented number – Access has a special data type that works well for this purpose, the AutoNumber data type.
  • A foreign key is used to connect to a record from another table – to create a relationship. Records might have the same or different values of a foreign key. Foreign keys have the same or a compatible data type as the primary key to which they connect.

For more information about key fields, see the article Create or modify a primary key

Benefits of normalized data

Data that is stored in separate tables to avoid redundancy is sometimes called normalized data. There are different degrees of normalization, called normal forms: first normal form, second normal form, etc. – with the data in each being progressively more normalized. The main benefits of normalized data are achieved with the third normal form, and include the following:

  • Error reduction When you enter the same information over and over again, it is possible for mistakes to creep into your data. Normalized data helps prevent this by allowing information to be entered once and reused as needed.
  • Reduced file size Because each fact is only stored once, normalized data saves disk space.
  • Improved performance Data that is normalized can be processed more efficiently, because data can be selected more precisely.

Normalizing is an investment

When you import data from a source that is not normalized, it takes a bit of work to normalize it. However, you only have to normalize the data once – the first time you import. In subsequent imports you can use queries to move imported data into the normalized tables that you created the first time. In this sense, normalization is an investment: work up front to reap benefits down the line.

For more information about normalization and database design, see the article Database design basics.

Access provides a wizard that can analyze a table and normalize it for you: the Table Analyzer Wizard. Next, we’ll walk through the process of using the Table Analyzer to normalize data, using the sample data as imported in the preceding section.

Use the Table Analyzer Wizard

When you import data from a large external source, it might not be normalized. You can use the Table Analyzer to normalize data in Access. The Table Analyzer Wizard creates new tables to hold the data.

Increase the number of file locks

Before you run the wizard, you may need to increase the value of the MaxLocksPerFile registry key. The default number of this key is 9500, which is too small to accommodate splitting the sample data. To change the registry setting, use the following steps.

  1. Click Start, and then click Run.
  2. Type regedit, and then click OK.
  3. Do one of the following:

If you are using 32-bit Windows    Locate the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE

If you are using a 64-bit Windows operating system    Locate the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE

  1. In the right pane of Registry Editor, double click MaxLocksPerFile.
  2. In the Edit DWORD Value dialog box, click Decimal.
  3. Make sure the value in the Value data box is at least 16000, and then click OK.

 Note    Some network environments may impose a limit on the number of locks per file. If you are attached to an office network, check with your network administrator before changing this registry setting.

  1. Restart Access, and open the database that contains the sample data.

Run the wizard

 Note    You only have to run the Table Analyzer Wizard once to create the tables. For future imports, you can use queries to move data into the normalized structure.

  1. On the Database Tools tab, in the Analyze group, click Analyze Table. The Table Analyzer Wizard starts.
  2. On the first screen of the wizard, you can read about the problems of data that isn’t normalized, and see some examples. When you are ready to proceed, click Next.
  3. On the second screen, you can read about what the wizard does, the benefits of normalization, and see some examples. When you are ready to proceed, click Next.
  4. On the next screen, in the Tables box, click tblImport and then click Next.
  5. On the next screen, you can choose whether to let the wizard decide how to normalize the tables. You should let the wizard decide unless you are familiar with the normalization process. For now, click Yes, let the wizard decide, and then click Next.
  6. On the next screen, the wizard displays a proposed set of normalized tables, and provides an opportunity to adjust the fields of and rename the tables. You should rename the proposed tables to indicate the data each contains. Do the following:
  1. Click Table1, and then click the Rename Table button (the button with a pencil and a table icon near the upper right corner of the wizard). In the dialog box that appears, type tblOrderDetails in the Table Name box, and then click OK.
  2. Click Table2, click the Rename Table button, type tblProductName in the Table Name box, and then click OK.
  3. Click Table3, click the Rename Table button, type tblSalesRegion in the Table Name box, and then click OK.
  4. Click Table4, click the Rename Table button, type tblProductCategory in the Table Name box, and then click OK.
  5. Click Next.
  1. On the next screen, the wizard continues to display the proposed set of normalized tables, and provides an opportunity to specify the primary key for each table. On the tblOrderDetails table, click ID. Then, click the Set Unique Identifier button (the button with only a key icon near the upper right corner of the wizard), and then click Next.
  2. On the next few screens, the wizard attempts to correct records that might have typographical errors. There is one such screen for each proposed table. This correction process can be useful, but sometimes there really are two values that just happen to be very similar – this is the case with the sample data. For each listed value on each of the correction screens, do the following:
  • Click the arrow in the Correction column, and then click (Leave as is) at the top of the list that appears.
  1. On the last screen of the wizard, click Yes, create the query, and then click Finish. Access creates a query that combines the normalized data as it appeared in the original table, and runs the query.

When the wizard is finished, Access Help opens and displays the article About the Table Analyzer.

Top of Page Top of Page

Create queries and a macro to manage data flow

After you have normalized your data and created a table structure, you can use append queries to move future data imports into the new tables. You can then use a macro to run the saved import, the append queries, and drop the imported table so the database is ready for future updates.

Create append queries to populate the new tables in future updates

An append query selects data from a source and adds it to an existing table. It does not affect the data that is already in the existing table. The new data is added as rows at the end of the existing table.

A drop-down list in an Append to row

When you append data to a set of related tables, the order of the queries matters because relationships may require that values already exist in some tables in order for records to be created in others. This is the case for the sample data. For example, the data in tblProductCategory must be appended before the data in tblProductName, because each product in the second table has a category that must already exist in the first table.

This is the order we will append the sample data:

  1. Product categories
  2. Product names
  3. Sales regions
  4. Order details

You can create an append query by using the query design grid, as described in the article Add records to a table by using an append query. When you work with your own imported data, you should use the design grid because it is easy to use.

You can also create a query by opening a blank query in SQL view and entering the necessary SQL. This is convenient and time-saving if you already know the SQL that you need. We’ll use this method to create the append queries using the sample data.

In the following procedures, we’ll look at SQL and what it does in the queries. For more information about SQL, see the article Introduction to Access SQL.

Open a new blank query in SQL view

  1. On the Create tab, in the Queries group, click Query Design.
  2. Close the Show Table dialog box.
  3. On the Design tab, in the Results group, click View, and then click SQL View.
  4. Delete the word SELECT and the semicolon from the new query.

Append data to tblProductCategory

  1. Open a new blank query in SQL View.
  2. Paste the following SQL into the query:

INSERT INTO tblProductCategory ( ProductCategory )
SELECT DISTINCT tblImport.ProductCategory AS Expr1
FROM tblImport;

  1. Press CTRL+S. In the Save As dialog box, type qryAppendProductCategoryFromMonthlyImport, and then click OK.
  2. On the Design tab, in the Results group, click Run.
  3. Access displays a message confirming that you want to append the data. Click Yes.

Let’s examine the SQL for this query line by line.

INSERT INTO tblProductCategory ( ProductCategory )    This line is an INSERT INTO statement. It tells Access to append data into the ProductCategory field of the tblProductCategory table.

SELECT DISTINCT tblImport.ProductCategory AS Expr1    This line is a SELECT clause. It tells Access from which field to select values – in this case, the ProductCategory field. The DISTINCT key word tells Access that each value should only be included once, so duplicate values are ignored.

FROM tblImport;    This line is a FROM clause. It tells Access from which data source to select the values – in this case, the object tblImport, the query that the Table Analyzer Wizard created after normalizing the imported data. Note the semicolon at the end of this line – this tells Access to run the SQL and is required.

Append data to tblProductName

  1. Open a new blank query in SQL View.
  2. Paste the following SQL into the query:

INSERT INTO tblProductName ( ProductName, tblProductCategory_ID )
SELECT DISTINCT tblImport.ProductName AS Expr1,
(SELECT TOP 1 [tblProductCategory].[ID]
FROM [tblProductCategory]
WHERE [tblImport].[ProductCategory]=[tblProductCategory].[ProductCategory]) AS Expr2
FROM tblImport;

  1. Press CTRL+S. In the Save As dialog box, type qryAppendProductNamesFromMonthlyImport, and then click OK.
  2. On the Design tab, in the Results group, click Run.
  3. Access displays a message confirming that you want to append the data. Click Yes.

Let’s look at the parts of this query that didn’t appear in the first query.

(SELECT TOP 1 [tblProductCategory].[ID]    This SELECT clause is the first line of a subquery – a query inside another query. You enclose a subquery in parentheses. The key words TOP 1 indicate that only the first value should be returned. For more information about using subqueries, see the article Nest a query inside another query or in an expression by using a subquery.

WHERE [tblImport].[ProductCategory]=[tblProductCategory].[ProductCategory]    This line is a WHERE clause is part of the subquery. A WHERE clause provides criteria for Access to use to limit the query results. Only records that meet the criteria are included in the results.

Append data to tblSalesRegion

  1. Open a new blank query in SQL View.
  2. Paste the following SQL into the query:

INSERT INTO tblSalesRegion ( TerritoryName, CountryRegionCode )
SELECT DISTINCT tblImport.TerritoryName AS Expr1, tblImport.CountryRegionCode AS Expr2
FROM tblImport;

  1. Press CTRL+S. In the Save As dialog box, type qryAppendRegionFromMonthlyImport, and then click OK.
  2. On the Design tab, in the Results group, click Run.
  3. Access displays a message confirming that you want to append the data. Click Yes.

There aren’t any clauses in this query that we didn’t already see in the first two queries.

Append data to tblOrderDetails

  1. Open a new blank query in SQL View.
  2. Paste the following SQL into the query:

INSERT INTO tblOrderDetails ( SalesOrderID, OrderDate, OrderQty, UnitPrice, StandardCost, tblProductName_ID, tblSalesRegion_ID )
SELECT tblImport.SalesOrderID AS Expr1, tblImport.OrderDate AS Expr2, tblImport.OrderQty AS Expr3, tblImport.UnitPrice AS Expr4, tblImport.StandardCost AS Expr5,
(SELECT TOP 1 [tblProductName].[ID]
FROM [tblProductName]
WHERE [tblImport].[ProductName]=[tblProductName].[ProductName]) AS Expr6,
(SELECT TOP 1 [tblSalesRegion].[ID]
FROM [tblSalesRegion]
WHERE [tblImport].[TerritoryName]=[tblSalesRegion].[TerritoryName]) AS Expr7
FROM tblImport;

  1. Press CTRL+S. In the Save As dialog box, type qryAppendOrderDetailsFromMonthlyImport, and then click OK.
  2. On the Design tab, in the Results group, click Run.
  3. Access displays a message confirming that you want to append the data. Click Yes.

Although this is the most complicated of the four append queries, it doesn’t have any clauses that we didn’t already see in the first two queries.

Create a query that lists which months have already been imported

To help avoid errors, we’ll create a query that checks the existing data to determine which months are already represented. When we create the macro, we’ll use this query to avoid importing the same month’s data twice.

  1. Open a new blank query in SQL View.
  2. Paste the following SQL into the query:

SELECT Format([tblOrderDetails].[OrderDate],"mmmm yyyy") AS MonthYear
FROM tblOrderDetails
GROUP BY Format([tblOrderDetails].[OrderDate],"mmmm yyyy"), DateSerial(Year([OrderDate]),Month([OrderDate]),1)
ORDER BY DateSerial(Year([OrderDate]),Month([OrderDate]),1);

  1. Press CTRL+S. In the Save As dialog box, type qryMonthsAlreadyImported, and then click OK.

Let’s look at the two clauses we haven’t seen before: GROUP BY and ORDER BY.

GROUP BY Format([tblOrderDetails].[OrderDate],"mmmm yyyy"), DateSerial(Year([OrderDate]),Month([OrderDate]),1)    This line is a GROUP BY clause. It tells Access that instead of displaying values for all records, it should only display each value one time for the fields (or in this case, the expressions) that are in the clause. This clause is necessary because the Order Details table has many orders in every month. Note that in addition to the expression we used in the SELECT clause of this query, we also need to include the expression from the ORDER BY clause that follows.

For more information about using GROUP BY, see the article GROUP BY Clause.

ORDER BY DateSerial(Year([OrderDate]),Month([OrderDate]),1)    This line is an ORDER BY clause. It tells Access to sort the results in ascending order using the values of the field (or in this case, the expression) that is in the clause.

For more information about using ORDER BY, see the article ORDER BY Clause.

Delete the tblImport query and the tblImport_OLD table

When we used the Table Analyzer Wizard to normalize the data, the table created by the saved import specification was renamed and replaced by a query. We already used the query (named tblImport) in the append queries. It’s a best practice to use three-letter prefixes with object names to indicate the type of object – that’s why the append queries all have names that start with qry.

Because we already have the normalized tables that we need and have imported the data into them, we’ll drop the table that was renamed and the query that replaced it. The next time the saved import runs, the tblImport table will be created again, and will serve as the data source for the append queries.

  1. In the Navigation Pane, right-click tblImport, and then click Delete. Click Yes in the dialog box that appears.
  2. In the Navigation Pane, right-click tblImport_OLD, and then click Delete. Click Yes in the dialog box that appears.

Create a macro that automates the import process end-to-end

Macros are an easy way to automate processes in Access. The following video provides an introduction.

You can use a macro to automate the process of importing data. You can then run the macro, instead of running a series of queries.

  1. On the Create tab, in the Macros & Code group, click Macro.
  2. On the Design tab, in the Show/Hide group, click Show All Actions.
  3. In the new macro, click Add New Action, type On, and then press ENTER. Access completes typing the OnError macro action, and displays the Go to and Macro Name arguments.
  4. Select Macro Name in the Go to argument.
  5. In the Macro Name argument, type ErrorHandler, and then press ENTER.
  6. Click Add New Action, type RunS, and then press ENTER. Access automatically completes typing the RunSavedImportExport macro action, and displays the Saved Import Export Name argument.
  7. Select Import-MonthlySalesData in the Saved Import Export Name argument.
  8. Click Add New Action, type SetW, and then press ENTER. Access completes typing the SetWarnings macro action, and displays the Warnings On argument. Leave the argument set to No.
  9. Click Add New Action, type If, and then press ENTER. Access creates an If block. An If block lets a macro evaluate an expression and perform different actions depending on the result of that evaluation. For more information, see the MSDN article If...Then...Else Macro Block.
  10. Click Conditional expression, and then enter the following expression:
    IsNull(DLookUp("[MonthYear]","qryMonthsAlreadyImported","[MonthYear] = '" & Format(DMax("[OrderDate]","[tblImport]"),"mmmm yyyy") & "'"))

The expression checks to see whether the month and year values in the table tblImport do not show up in the results of the query qryMonthsAlreadyImported. If so, then the macro performs the actions in the If block. If not, the macro does not perform the actions.

  1. Click Add New Action, type OpenQ, and then press ENTER. Access completes typing the OpenQuery macro action, and displays the Query Name, View, and Data Mode arguments.
  2. Select qryAppendProductCategoryFromMonthlyImport in the Query Name argument. Do not change the other two arguments.
  3. Click Add New Action, type OpenQ, and then press ENTER. Access completes typing the OpenQuery macro action, and displays the Query Name, View, and Data Mode arguments.
  4. Select qryAppendProductNamesFromMonthlyImport in the Query Name argument. Do not change the other two arguments.
  5. Click Add New Action, type OpenQ, and then press ENTER. Access completes typing the OpenQuery macro action, and displays the Query Name, View, and Data Mode arguments.
  6. Select qryAppendRegionFromMonthlyImport in the Query Name argument. Do not change the other two arguments.
  7. Click Add New Action, type OpenQ, and then press ENTER. Access completes typing the OpenQuery macro action, and displays the Query Name, View, and Data Mode arguments.
  8. Select qryAppendOrderDetailsFromMonthlyImport in the Query Name argument. Do not change the other two arguments.
  9. In the lower right corner of the If block, click Add Else. Access creates an Else block. An Else block always follows an If block, and contains the actions the macro performs if the expression in the IF block is not true.
  10. In the Else block, click Add New Action, type Mes, and then press ENTER. Access completes typing the MessageBox macro action, and displays the Message, Beep, Type, and Title arguments.
  11. In the Message argument, click Required, and then enter the following expression:
    ="Data for the month of " & Format(DMax("[OrderDate]","[tblImport]"),"mmmm yyyy") & " has already been imported." This expression retrieves the month and year from the table tblImport and adds text to either side of it to produce a string that serves as the message Access displays if the data has already been imported.
  12. Select Information in the Type argument. Do not change the Beep or Title arguments.
  13. Click Add New Action, type RunSQ, and then press ENTER. Access completes typing the RunSQL macro action, and displays the SQL Statement and Use Transaction arguments.
  14. In the SQL Statement argument, type DROP TABLE [tblImport]. Do not change the Use Transaction argument.
  15. Click Add New Action, type SetW, and then press ENTER. Access completes typing the SetWarnings macro action, and displays the Warnings On argument.
  16. Select Yes in the Warnings On argument.
  17. Click Add New Action, type Sub, and then press ENTER. Access creates a Submacro block. A Submacro block contains a macro that can be run inside the macro that contains it. This Submacro block will contain a macro to handle any errors – we referred to it already near the beginning of this macro. For more information about submacros, see the article Video: Introduction to submacros.
  18. In the box next to Submacro:, enter ErrorHandler. This is the name of the submacro.
  19. In the Submacro block, click Add New Action, type Mes, and then press ENTER. Access completes typing the MessageBox macro action, and displays the Message, Beep, Type, and Title arguments.
  20. In the Message argument, enter the following expression:
    ="An error occurred." & Chr(10) & Chr(10) & "Error Number: " & [MacroError].[Number] & Chr(10) & Chr(10) & "Error Description: " & [MacroError].[Description]

This expression creates a message that specifies the error number and description when an error occurs.

  1. Select Critical in the Type argument. Do not change the Beep or the Title arguments.
  2. Press CTRL+S. In the Save As dialog box, type mcrImportMonthlyData, and then click OK.

For more information about creating macros, see the article Create a user interface (UI) macro, and the section Further reading.

Import additional months of data

Now that we’ve got a macro automating much of the process, let’s walk through using it to import the remaining data.

  1. In Windows, browse to C:\MonthlySalesReport\MonthlyData\.
  2. Rename the file MonthlySalesData.txt to January 2010Sales_imported.txt.
  3. Move the file named February2010Sales.txt from the MonthlyData folder to the MonthlySalesReport folder, and rename the copied file MonthlySalesData.txt.
  4. In Access (with your version of the sample database open), in the Navigation Pane, double-click mcrImportMonthlyData.
  5. Repeat steps 2 through 4, but each time use the next month. For example, the second time, in step 2 you rename the file that has February in its filename, and in step 3 you copy the file that has March in its filename.
  6. Continue repeating the steps until you have imported all the data. This will be apparent because there will be no more files in the MonthlyData folder.

Top of Page Top of Page

Create and distribute reports

After you get your data into normalized tables and set everything up for future imports, it’s time to create reports – the summary you can distribute to other people to help them make decisions.

Overview of reports

A report is a database object that you use to display and summarize data. Reports provide a way to distribute or archive snapshots of your data.

Employee report in Print Preview

Reports can provide details about individual records, summaries across many records, or both. You can also use Access reports to create labels for mailings or other purposes.

Parts of a report

In Access, the design of a report is divided into sections. In a client database, you can view your report in Design view to see its sections. In Layout view, the sections are not as obvious, but they are still there, and can be selected by using the drop-down list in the Selection group on the Format tab. To create useful reports, you need to understand how each section works. For example, the section in which you choose to place a calculated control determines how Access calculates the results. The following list is a summary of the section types and their uses:

Report Header    This section is printed just once, at the beginning of the report. Use the report header for information that might normally appear on a cover page, such as a logo, a title, or a date. When you place a calculated control that uses the Sum aggregate function in the report header, the sum calculated is for the entire report. The report header is printed before the page header.

Page Header    This section is printed at the top of every page. For example, use a page header to repeat the report title on every page.

Group Header    This section is printed at the beginning of each new group of records. Use the group header to print the group name. For example, in a report that is grouped by product, use the group header to print the product name. When you place a calculated control that uses the Sum aggregate function in the group header, the sum is for the current group. You can have multiple group header sections on a report, depending on how many grouping levels you have added.

Detail    This section is printed once for every row in the record source. This is where you place the controls that make up the main body of the report.

Group Footer    This section is printed at the end of each group of records. Use a group footer to print summary information for a group. You can have multiple group footer sections on a report, depending on how many grouping levels you have added.

Page Footer    This section is printed at the end of every page. Use a page footer to print page numbers or per-page information.

Report Footer    This section is printed just once, at the end of the report. Use the report footer to print report totals or other summary information for the entire report.

For a more thorough overview of reports, see the article Introduction to reports.

Next, we’ll create the following three reports and queries to supply them with data:

  • Sales by Product Category
  • Sales by Quarter
  • Total Sales by Region

Report: Sales by Product Category

A common reporting need is to summarize data across a set of attributes. For example, a sales organization might want to see sales summed across the categories of products sold.

The following procedures show how to create a query data source and a report that displays sales by product category using the sample data.

Create the Sales by Category query

  1. On the Create tab, in the Queries group, click Query Design.
  2. In the Show Table dialog box, double-click tblOrderDetails, tblProductName, and tblProductCategory, and then click Close. Access adds the tables to the query design window.
  3. On the tblProductCategory object, double-click ProductCategory. Access adds the field to the query grid.
  4. Right-click the second column in the query grid, and then click Zoom.
  5. In the Zoom box, type Gross Sales: OrderQty*UnitPrice, and then click OK.
  6. Right-click the third column in the query grid, and then click Zoom.
  7. In the Zoom box, type Net Sales: (OrderQty*UnitPrice)-(OrderQty*StandardCost), and then click OK.
  8. On the Design tab, in the Show/Hide group, click Totals. The Total row appears in the query grid.
  9. In the Total row, select Sum in the second and third columns.
  10. On the Design tab, in the Results group, click Run. The query runs, and displays gross and net sales for each of the 35 product categories.
  11. Press CTRL+S, type qrySalesByCategory in the Save As box, and then click OK.

Create the Sales by Category report

  1. In the Navigation Pane, click qrySalesByCategory.
  2. On the Create tab, in the Reports group, click Report. Access creates a report based on the selected query and opens it in Layout view.
  3. Adjust the layout of the report to make it easier to read. You can adjust the width of a layout column by dragging its edge. Consider changing the labels to make them easier to read, such as by adding spaces and removing unnecessary elements.
  4. Press CTRL+S, type rptSalesByCategory in the Save As box, and then click OK.

Report: Sales by Region

Another common attribute that a sales organization wants to summarize across is region. The following procedures show how to create a query and report that display gross and net sales by region using the sample data.

Create the Sales by Region query

  1. On the Create tab, in the Queries group, click Query Design.
  2. In the Show Table dialog box, double-click tblOrderDetails and tblSalesRegion, and then click Close. Access adds the tables to the query design window.
  3. On the tblSalesRegion object, double-click TerritoryName. Access adds the field to the query grid.
  4. Right-click the second column in the query grid, and then click Zoom.
  5. In the Zoom box, type Gross Sales: OrderQty*UnitPrice, and then click OK.
  6. Right-click the third column in the query grid, and then click Zoom.
  7. In the Zoom box, type Net Sales: (OrderQty*UnitPrice)-(OrderQty*StandardCost), and then click OK.
  8. On the Design tab, in the Show/Hide group, click Totals. The Total row appears in the query grid.
  9. In the Total row, select Sum in the second and third columns.
  10. On the Design tab, in the Results group, click Run. The query runs, and displays gross and net sales for each of the 10 sales regions.
  11. Press CTRL+S, type qrySalesByRegion in the Save As box, and then click OK.

Create the Sales by Region report

  1. In the Navigation Pane, click qrySalesByRegion.
  2. On the Create tab, in the Reports group, click Report. Access creates a report based on the selected query and opens it in Layout view.
  3. Adjust the layout of the report to make it easier to read. You can adjust the width of a layout column by dragging its edge. Consider changing the labels to make them easier to read, such as by adding spaces and removing unnecessary elements.
  4. Press CTRL+S, type rptSalesByRegion in the Save As box, and then click OK.

Report: Sales by Quarter

Another common reporting need is to summarize data over equal time periods, such as quarters. For example, a sales organization might want to see total sales per quarter.

The following procedures show how to create a query and report that display the gross and net sales per quarter using the sample data.

Create the Sales by Quarter query

  1. On the Create tab, in the Queries group, click Query Design.
  2. In the Show Table dialog box, double-click tblOrderDetails, and then click Close. Access adds the table to the query design window.
  3. Right-click the first column in the query grid, and then click Zoom.
  4. In the Zoom box, type Quarter: "Q" &Format(OrderDate,'q')&" - "&Format(OrderDate,'yyyy'), and then click OK.
  5. Right-click the second column in the query grid, and then click Zoom.
  6. In the Zoom box, type Gross Sales: OrderQty*UnitPrice, and then click OK.
  7. Right-click the third column in the query grid, and then click Zoom.
  8. In the Zoom box, type Net Sales: (OrderQty*UnitPrice)-(OrderQty*StandardCost), and then click OK.
  9. On the Design tab, in the Show/Hide group, click Totals. The Total row appears in the query grid.
  10. In the Total row, select Sum in the second and third columns.
  11. On the Design tab, in the Results group, click Run. The query runs, and displays gross and net sales for each of the 10 sales regions.
  12. Press CTRL+S, type qrySalesByQuarter in the Save As box, and then click OK.

Create the Sales by Quarter report

  1. In the Navigation Pane, click qrySalesByQuarter.
  2. On the Create tab, in the Reports group, click Report. Access creates a report based on the selected query and opens it in Layout view.
  3. Adjust the layout of the report to make it easier to read. You can adjust the width of a layout column by dragging its edge. Consider changing the labels to make them easier to read, such as by adding spaces and removing unnecessary elements.
  4. Press CTRL+S, type rptSalesByQuarter in the Save As box, and then click OK.

Other kinds of reports

The preceding reports only touch on some of the things that you can do with Access reports. For information about other features, options, and design techniques, see the following articles:

Create a grouped or summary report

Create and use subreports

Print a report

Often, you want to distribute reports on paper. Access reports are optimized for printing, and generally look better printed than other objects, such as tables or forms.

  1. Right-click the report that you want to print, and then click Print Preview. Access opens the report in Print Preview mode.
  2. If you need to adjust the report, right-click the previewed report, and then click Layout View. After you make any changes, on the Design tab, in the Views group, click View, and then click Print Preview.
  3. When you are ready to print, on the Print Preview tab, in the Print group, click Print.

Save a report as PDF or XPS

You may prefer to send a report via email, or transfer it to a disk separately from the whole database. You can save a report as a PDF or XPS file for such purposes.

  1. In the Navigation Pane, double-click the report that you want to save as PDF or XPS.
  2. Click the File tab, and then click Save & Publish.
  3. Under File Types, click Save Object As.
  4. Under Save the current database object, click PDF or XPS, and then click Save As.
  5. In the Publish as PDF or XPS dialog box, browse to the location where you want to save the new file, select the file type that you want in the Save as type box, and then click Publish.

 Note    You may want to set options, such as the page range or whether to include non-printing information. If so, click Options before you click Publish.

For more information about saving as PDF, see the article Save as PDF. For more information about saving as XPS, see the article Save as XPS.

Top of Page Top of Page

Explore ways to get more out of Access

This section contains links to information about additional Access features and techniques. While they are not covered in the article, you can use these techniques to refine the database that you built using the sample data.

For example, the form that displays when you open the sample database is a navigation form (listed below, under More kinds of forms), and it displays when the database opens because it is the default form (listed below, under Form options and techniques).

Report design options and techniques

Video: Introduction to form and report layouts

Video: Use conditional formatting on reports

Video: Add, reuse, and update images on forms and reports

Insert a page break control in an Access report

Video: Improve the appearance of your Access forms and reports with Office Themes

Form design

The sample database contains several forms that help make it more usable. To learn more about creating and using forms, see the following articles:

Basic form design

Introduction to forms

Create a form by using the Form tool

Which view should I use: Layout view or Design view?

More kinds of forms

Create a tabbed form

Create a split form

Create a navigation form

Create a form that contains a subform

Form options and techniques

Set the default form that appears when you open a database or Web application

Create a control that references a control on another form

Add Web browsing to a form

Store the date and time when a record is modified

Make a query ask for input

Macro design

Video: Introduction to the Macro Builder

Create a macro that runs when you open a database

Video: Create a data macro

Becoming an Access developer

If you’d like to learn more about developing solutions with Access, see these articles:

Introduction to Access programming

Get help for built-in functions, properties, macro actions, and SQL keywords

Top of Page Top of Page

 
 
Applies to:
Access 2010