Set the record source for a report

To choose the source of data for a report, open the report in Layout view and press Alt + Enter to open the property sheet. Click the square just below the report object tab to select the entire report, and then on the property sheet, on the Data tab, click Record Source to select the property. You can then set the record source by choosing an item from the list or by clicking the Build button Button image to open query Design view, where you can build the record source as you would any query.

The record source of a report determines which fields from your database can be displayed on the report. In a relational database, related data is usually spread out across multiple tables. For example, information about customers is stored in one table, and information about orders is stored in another table. Reports give you a flexible way to "reassemble" the data and display it in just the way you want, and can be optimized for printing. For more information about report record sources, see the rest of this article. For an overview of reports, see the article Introduction to reports in Access.

Note: This article doesn't apply to Access web apps – the kind of database you design with Access and publish online. See Create an Access app for more information.

Different types of record sources

Table

If all of the fields you need are in a single table, you can use that table as the record source of the report. If the fields you need are contained in two or more tables, you will need to make sure that the tables can be joined logically on some kind of common value, and then create a named query or an embedded query to use as the record source.

Named query

A named query – usually just called a query – is a query that has been saved as a database object. You can use a named query as a record source for multiple forms or reports. However, changes to the query design will affect all forms and reports that use it as a record source. Moreover, it's relatively easy to delete a named query, which breaks any forms or reports that use it.

Embedded query

An embedded query is a query that is stored in the Record Source property of another object. Because an embedded query isn't a separate object, it's less likely that you'll break the report by accidentally deleting or modifying its query. Creating an embedded query is recommended if you want the query to be dedicated only to the report (in other words, you won't want to share the query with any other objects).

Embedded queries are created by default when you use the Report Design, Blank Report, or Labels tools, described in the next section. Embedded queries are also created by the Report Wizard, provided that you select data from more than one table for your report. If you only select data from one table, the Report Wizard binds the report directly to that table.

You can also create an embedded query by copying the SQL statement from a named query that is open in SQL View, and then pasting the SQL statement into the Record Source property of a report. To open a named query in SQL View:

Right-click the query in the Navigation Pane, and then click Design View.
On the Design tab, in the Results group, click View, and then click SQL View.

Top of Page

How the report tools create record sources

To create a report in Access, you click one of the tools in the Reports group of the Create tab. Depending on how you use the tools, each one creates one of the three record source types described in the preceding section.

The following table describes what each report tool does, and what record source types are created by default.

Tool	Description	Default record source type
Report	Creates a simple, tabular report containing all of the fields in the data source.	Table or named query, which you must select in the Navigation Pane prior to clicking the tool.
Report Design	Opens a blank report in Design view, to which you can add just the fields and controls you want.	Embedded query, if you drag fields from the Field List task pane to the report. You can also select a table or named query from the Record Source drop-down list in the Property Sheet.
Blank Report	Opens a blank report in Layout view, and displays the Field List task pane. As you drag fields from the Field List to the report, Access creates an embedded query and stores it in the Record Source property of the report.	Embedded query, if you drag fields from the Field List task pane to the report. You can also select a table or named query from the Record Source drop-down list in the Property Sheet.
Report Wizard	Displays a multiple-step wizard that lets you specify fields, grouping/sorting levels, and layout options. The wizard creates a report based on the selections you make.	Embedded query, if you select fields from more than one table. If you only select fields from a single table, Access uses that table as the record source.
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. The wizard creates the label report based on the selections you make.	Table or named query, which you must select prior to clicking the tool.

Note: The Report Design, Report Wizard, and Labels tools all create "Client" reports, which are not compatible with web databases. For more information about web databases, see the article Edit or publish a web database site on SharePoint.

Top of Page

Work with record sources

Convert a record source to an embedded query

Any report that is bound to a table or named query can be modified so that it is bound to an embedded query instead. As mentioned earlier, this can make a database easier to maintain by making reports more self-contained, and less dependent on other objects. Depending on your current record source type, use one of the following methods to convert it to an embedded query:

Method 1: Click the Build button

If the Record Source property is a table name, you can quickly create an embedded query by clicking the Build button in the Record Source property box.

In the Navigation Pane, right-click the report you want to modify, and then click Layout View.
Right-click anywhere on the report, and then click Report Properties.
On the All tab, click in the Record Source property box, and then click the Build button .
Access asks if you want to create a query based on the table. Click Yes to continue.

Access opens the Query Builder and adds the table that was originally in the Control Source property.
Add fields to the query grid by double-clicking each field name that you want on the report. This must be done for all fields that you want to be displayed on the report.
To add fields from related tables or queries:
1. On the Design tab, in the Query Setup group, click Show Table.
2. In the Show Table dialog box, select the table or query that you want to add, and then click Add.
3. Click Finish to close the Show Table dialog box.
4. Make sure the tables and queries you add are joined in a logical way. For example, the ID field of the Customer table would likely have a join line connecting it to the CustomerID field of the Orders table. You can create join lines by dragging a field name from one table or query to a field name in another table or query.
  
  For more information about creating queries, see the article Introduction to queries.
5. Add fields to the query grid by double-clicking each field name that you want on the report. This must be done for all fields that you want to be displayed on the report.
To test your query:
1. On the Design tab, in the Results group, click View, and then click Datasheet View.
2. To return to Design view, on the Design tab, in the Results group, click View, and then click Design View.
When you are satisfied that the data returned by the query is the data you want on your report, on the Design tab, in the Close group, click Close.
Click Yes to save the changes made to the SQL statement and update the property.

Note: If the new query does not contain all the fields that were used on the report, Access will prompt you for a parameter value for each missing field. You can either delete the reference to the field on the report, or use the procedure under Edit an embedded query to add the missing fields to the query.

Method 2: Add a field from a related table by using the field list

In the Navigation Pane, right-click the report you want to modify, and then click Layout View.
If the Field List task pane is not already displayed, on the Design tab, in the Tools group, click Add Existing Fields.
If Show all tables is displayed at the top of the field list, click it to display fields in related tables and other tables.
Under Fields available in related tables, expand a table, and drag one of its fields to the report.

Access changes the record source to an embedded query that contains the field you added. You can continue adding fields in this manner, or edit the embedded query by using the procedure in Edit an embedded query.

Method 3: Copy and paste SQL from a named query

In the Navigation Pane, right-click the query that contains the SQL statement you want to copy, and then click Design View.
On the Home tab, in the Views group, click View, and then click SQL View.
Copy the text in the SQL pane, and then close the query without saving.
In the Navigation Pane, right-click the report to which you want to add the embedded query, and then click Layout View.
If the property sheet is not already displayed, press F4 to display it.
On the All tab, select the text that is in the Record Source property, and then press CTRL+P to paste the SQL into the property box.

Method 4: Copy and paste SQL from another form or report

You can copy an embedded query directly from the Record Source property of one object to another. That way, if another form or report contains an embedded query that returns the data you want, it's easy to reuse it for the report you're working on.

In the Navigation Pane, right-click the form or report that contains the embedded SQL statement you want to copy, and then click Layout View.
Right-click anywhere on the form or report, and then click Form Properties or Report Properties.
On the All tab, select the entire SQL statement text in the Record Source property box, and then press CTRL+C to copy it.
Close the form or report without saving.
In the Navigation Pane, right-click the report to which you want to add the embedded query, and then click Layout View.
Right-click anywhere on the report, and then click Report Properties.
On the All tab, select the text that is in the Record Source property, and then press CTRL+P to paste the SQL into the property.

Note: If the new query does not contain all the fields that were used on the report, Access will prompt you for a parameter value for each missing field. You can either delete the reference to the field on the report, or use the following procedure (Edit an embedded query) to add the missing fields to the query.

Edit an embedded query

As your database grows, you might need to modify the record source of a report, for example, to add more fields to a report. Use this procedure to open the record source in the Query Builder:

In the Navigation Pane, right-click the report that contains the embedded query that you want to edit, and then click Layout View.
Right-click anywhere on the report, and then click Report Properties.
On the All tab, click in the Record Source property box, and then click the Build button .

Access opens the query in the Query Builder.
Edit the query as needed, and then on the Design tab, in the Close group, click Close.
Click Yes to save the changes and update the property.

Access closes the Query Builder. Any changes you made in the Query Builder will be reflected in the SQL statement in the Record Source property.

For more information about creating queries, see the article Introduction to queries.

Save an embedded query as a named query

You might decide that the embedded query that is serving as the record source for a report might also be useful for some other purpose. In that case, you can save it as a named query which will then be available for use by other objects.

In the Navigation Pane, right-click the report that contains the embedded query, and then click Layout View.
Right-click anywhere on the report, and then click Report Properties.
On the All tab, click in the Record Source property box, and then click the Build button .

Access opens the query in the Query Builder.
On the Design tab, in the Close group, click Save As.
In the Save As dialog box, type a query name in the top box, and then click OK.

Access saves the query as a named query in the Navigation Pane.

On the Design tab, in the Close group, click Close.

Access asks if you want to save the changes made to the embedded query, even if you didn't make any changes. In most cases, you should just click No to close the Query Builder. However, if you did make changes that you want to be reflected in the report's embedded query, click Yes.

Top of Page

Microsoft Office Tutorials

Tuesday, May 29, 2018