I have a report on Access which has a Value field (eg £100,000) for the value of a property and a Type field (eg purchase) which displays the property type. Home > Software > Microsoft Office. Sum based on a criteria in Access Reports. By David2959 on Jul 6, 2010 at 06:00 UTC. Microsoft Office. Next: Excel Macro Help. Demonstrates two methods that you can use to create a running totals query in Microsoft. For a Microsoft Access 2000. To create a running sum.
Reports offer a way to view, format, and summarize the information in your Microsoft Access database. For example, you can create a simple report of phone numbers for all your contacts, or a summary report on the total sales across different regions and time periods.
From this article, you’ll get an overview of reports in Access. You’ll also learn the basics of creating a report, and using options like sorting, grouping, and summarizing the data, and how to preview and print the report.
Note: The information in this article is intended for use only with Access desktop databases. Access web apps don't support reports.
In this article
Overview of reports in Access
What can you do with a report?
A report is a database object that comes in handy when you want to present the information in your database for any of the following uses:
Display or distribute a summary of data.
Archive snapshots of the data.
Provide details about individual records.
Create labels.
Parts of a report
While it is possible to create “unbound” reports that do not display data, but for the purposes of this article, we’ll assume that a report is bound to a data source such as a table or query. The design of a report is divided into sections that you can view in the Design view. Understanding how each section works can helps you create better reports. 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:
Section | How the section is displayed when printed | Where the section can be used |
|---|---|---|
Report Header | 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 | At the top of every page. | Use a page header to repeat the report title on every page. |
Group Header | 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. For more information about creating group headers and footers, see the section Add grouping, sorting, or totals. |
Detail | Appears 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 | 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 | At the end of every page. | Use a page footer to print page numbers or per-page information. |
Report Footer | At the end of the report. Note: In Design view, the report footer appears below the page footer. However, in all other views (Layout view, for example, or when the report is printed or previewed), the report footer appears above the page footer, just after the last group footer or detail line on the final page. | Use the report footer to print report totals or other summary information for the entire report. |
You’ll find that it’s much easier to create meaningful reports when your database has a well-designed table structure and relationships. For an introduction to planning and designing a database, see the article Database design basics.
Create a report in Access
You can create reports for you Access desktop database by following the steps below:
Step 1: Choose a record source
The record source of a report can be a table, a named query, or an embedded query. The record source must contain all of the rows and columns of data you want display on the report.
If the data is from an existing table or query, select the table or query in the Navigation Pane, and then continue to Step 2.
If the record source does not yet exist, do one of the following:
Continue to Step 2 and use the Blank Report tool,
Or
Create the table(s) or query that contains the required data. Select the query or table in the Navigation Pane, and then continue to Step 2.
Step 2: Choose a report tool
The report tools are located on the Create tab of the ribbon, in the Reports group. The following table describes the options:
Tool | Description |
|---|---|
Report | Creates a simple, tabular report containing all of the fields in the record source you selected in the Navigation Pane. |
Report Design | Opens a blank report in Design view, to which you can add the required fields and controls. |
Blank Report | Opens a blank report in Layout view, and displays the Field List from where you can add fields to the report |
Report Wizard | Displays a multiple-step wizard that lets you specify fields, grouping/sorting levels, and layout options. |
Labels | Displays a wizard that lets you select standard or custom label sizes, as well as which fields you want to display, and how you want them sorted. |
Step 3: Create the report
Click the button for the tool you want to use. If a wizard appears, follow the steps in the wizard and click Finish on the last page.
Access displays the report in Layout view.Format the report to achieve the looks that you want:
Resize fields and labels by selecting them and then dragging the edges until they are the size you want.
Move a field by selecting it (and its label, if present), and then dragging it to the new location.
Right-click a field and use the commands on the shortcut menu to merge or split cells, delete or select fields, and perform other formatting tasks.
In addition, you can use the features described in the following sections to make your report more attractive and readable.
Add grouping, sorting, or totals
The fastest way to add grouping, sorting, or totals to a desktop database report is to right-click the field to which you want to apply the group, sort, or total, and then click the desired command on the shortcut menu.
You can also add grouping, sorting, or totals by using the Group, Sort, and Total pane while the report is open in Layout view or Design view:
If the Group, Sort, and Total pane is not already open, on the Design tab, in the Grouping and Totals group, click Group & Sort.
Click Add a group or Add a sort, and then select the field on which you want to group or sort.
Click More on a grouping or sorting line to set more options and to add totals.
For more detailed information about grouping, sorting, and totals, see the article Create a grouped or summary report.
Highlight data with conditional formatting
Access includes tools for highlighting data on a report. You can add conditional formatting rules for each control or group of controls, and in client reports, you can also add data bars to compare data.
To add conditional formatting to controls:
Example Of Microsoft Access Report Summary Options
Right-click the report in the Navigation Pane and click Layout View.
Select the required controls and on the Format tab, in the Control Formatting group, click Conditional Formatting.
Tip: To select multiple controls, hold down the CTRL key and click the controls.
In the Conditional Formatting Rules Manager dialog box, click New Rule.
In the New Formatting Rule dialog box, select a value under Select a rule type:
To create a rule that is evaluated for each record individually, select Check values in the current record or use an expression.
To create a rule that compares records to each other by using data bars, click Compare to other records.
Under Edit the rule description, specify the rule for when the formatting would be applied as well as what formatting should be applied, and then click OK.
To create an additional rule for the same control or set of controls, repeat this procedure from step 4.
Customizing color and fonts
Try an App Theme options to customize the color and fonts.
Open a report in Layout view by right-clicking it in the Navigation Pane and then clicking Layout View.
From the Report Layout Tools options, on the Design tab, click Themes and point the cursor over the various themes in the gallery to preview the effects. Click on a theme to select it, and then save your report.
Use the Colors or Fonts galleries to set colors or fonts independently.
Top of Page
Add a logo or background image
You can add a logo or background image to a report and If you update the image, the update is automatically made wherever the image is used in the database.
To add or remove an image:
In the Navigation Pane, right-click the report and click Layout View.
In the report, click the position where you want to add the image and on the Design tab, in the Header/Footer group, click Logo.
Navigate to the image, and click Open. Access adds the image to the report.
To remove the image, right-click the image and click Delete from the shortcut menu.
Example Of Microsoft Access Report Summary
To add a background image:
In the Navigation Pane, right-click the report and click Layout View.
On the Format tab, in the Background group, click Background Image.
Select an image from the Image Gallery list or click Browse, select an image, and then click OK.
Preview and print a report
Preview a report
Right-click the report in the Navigation Pane and click Print Preview. You can use the commands on the Print Preview tab to do any of the following:
Print the report
Adjust page size or layout
Zoom in or out, or view multiple pages at a time
Refresh the data on the report
Export the report to another file format.
Click Close Print Preview.
Print a report
To print a report without previewing it:
Right-click the report in the Navigation Pane and click Print. The report is sent to your default printer.
Note: If you select the report in the Navigation Pane and select Print from the File tab, you can select additional printing options such as number of pages and copies and specify a printer.
To open a dialog box where you can select a printer, specify the number of copies, and so on, click Print.
You can use the DSum functions to calculate the sum of a set of values in a specified set of records (a domain). Use the DSum function in Visual Basic, a macro, a query expression, or a calculated control.
For example, you could use the DSum function in a calculated field expression in a query to calculate the total sales made by a particular employee over a period of time. Or you could use the DSum function in a calculated control to display a running sum of sales for a particular product.
DSum(expr, domain, [criteria])
The DSum function has the following arguments.
| Argument | Description |
|---|---|
| expr | An expression that identifies the numeric field whose values you want to total. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field . In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function. |
| domain | A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter. |
| criteria | An optional string expression used to restrict the range of data on which the DSum function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DSum function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DSum function returns a Null. |
Remarks
If no record satisfies the criteria argument or if domain contains no records, the DSum function returns a Null.
Whether you use the DSum function in a macro, module, query expression, or calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.
You can use the DSum function to specify criteria in the Criteria row of a query, in a calculated field in a query expression, or in the Update To row of an update query.
Note You can use either the DSum or Sum function in a calculated field expression in a totals query. If you use the DSum function, values are calculated before data is grouped. If you use the Sum function, the data is grouped before values in the field expression are evaluated.
You may want to use the DSum function when you need to display the sum of a set of values from a field that is not in the record source for your form or report. For example, suppose you have a form that displays information about a particular product. You could use the DSum function to maintain a running total of sales of that product in a calculated control.
Tip
RunningSumDSum
Note Unsaved changes to records in domain aren't included when you use this function. If you want the DSum function to be based on the changed values, you must first save the changes by clicking Save Record on the Records menu, moving the focus to another record, or by using the Update method.
Example
The following example totals the values from the Freight field for orders shipped to the United Kingdom. The domain is an Orders table. The criteria argument restricts the resulting set of records to those for which ShipCountry equals UK.
The next example calculates a total by using two separate criteria. Note that single quotation marks (') and number signs (#) are included in the string expression, so that when the strings are concatenated, the string literal will be enclosed in single quotation marks, and the date will be enclosed in number signs.
You can use a domain function in the Update To row of an update query. For example, suppose you want to track current sales by product in a Products table. You could add a new field called SalesSoFar to the Products table, and run an update query to calculate the correct values and update the records. Create a new query based on the Products table, and click Update on the Query menu. Add the SalesSoFar field to the query grid, and enter the following in the Update To row:
When the query is run, Microsoft Access calculates the total amount of sales for each product, based on information from an Order Details table. The sum of sales for each product is added to the Products table.
See Also | DCount Function | Domain Aggregate Functions | Restrict Data to a Subset of Records | RunningSum Property