Access 2007 merges the results of multiple select queries with a union query

For multiple, similar select queries, you can use federated queries when you want to view all the data that they return together as a merged collection

This article shows you how to create a federated query based on two or more existing selection queries, and how to write a federated query using Structured Query Language (SQL)

To complete the examples in this article, you should have a basic understanding of how to create and run a select query. For more information about how to create a select query, see the links in the See also section

The content of this article

What are the capabilities of a federated query's basic Knowledge union query?

A union query merges the result sets of multiple similar select queries

For example, suppose you have two tables, one to store information about the customer, the other to store information about the vendor, and there is no relationship between the two tables. Also assume that both tables have fields that store contact information, and you want to view all of the contact information in both tables at the same time

You can create a select query for each table (select query: Ask questions about the data stored in the table, and then return a result set in the form of a datasheet without changing the data.) To retrieve only those fields that contain contact information, but the returned information will still be in two separate locations. To combine the results of two or more select queries into one result set, you can use a federated query

Requirements for federated queries

A selected query that is merged in a union query must have the same number of output fields, in the same order, and contain the same or compatible data type. When you run a federated query, the data from each group's corresponding fields is merged into one output field so that the query output contains the same number of fields as each Select statement

Note the number and text data types are compatible based on the purpose of the Federated query

Federated queries are SQL-specific. SQL-specific queries cannot be displayed in Design view, and therefore must be written directly in SQL. In Microsoft Office Access 2007, you can use the SQL View object tab to write SQL-specific queries, including federated queries

Tip Each query can be expressed in an SQL statement. In addition, most queries can also be used in the query design grid design grid: The grid that you use when you design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, the grid was formerly known as the QBE grid. , building queries will be easier if this environment is available. When you create a query using the design grid, you can always switch to SQL view to view the SQL statements that are processed when you run the query. It is a good practice to view queries in SQL view, because you can familiarize yourself with SQL and deepen your understanding of how queries work. In some cases, you can also use SQL view to troubleshoot queries that do not return the expected results

SQL syntax for federated queries

In a federated query, each select query (also known as a SELECT statement) has a SELECT clause and a FROM clause, and possibly a WHERE clause. The SELECT clause lists the fields that contain the data to retrieve; the FROM clause lists the tables that contain them; the WHERE clause lists the conditions for those fields. Select statements in union queries are grouped together with the Union keyword

For a federated query that incorporates two select queries, its basic SQL syntax is as follows:

SELECT field_1[, field_2,…] FROM table_1[, table_2,…] UNION [ALL] SELECT field_a[, field_b,...] FROM table_a[, table_b,…];

For example, suppose you have two tables named products and Services, respectively. Both tables have fields that contain the name of the product or service, price, warranty or guarantee terms, and whether the product or service is provided exclusively. Although the Products table stores warranty information and the Services table stores warranty information, the basic information is the same (i.e. whether a particular product or service complies with its quality commitment). You can use a union query to combine the four fields in two tables, for example:

SELECT name, price, warranty_available, exclusive_offer FROM Products UNION ALL SELECT name, price, guarantee_available, exclusive_offer FROM Services;

Let's check the syntax example above

Select name, Price, warranty_available, Exclusive_offer This is a SELECT clause that introduces the selection query. The SELECT is followed by a list of identifiers that indicate the fields from which to retrieve data. The SELECT clause must always list at least one field. This SELECT clause lists the field identifier name, price, warranty_available, and Exclusive_offer

From the products this is a FROM clause. The FROM clause follows the SELECT clause, which together form a basic SELECT statement. The From Post also has a list of identifiers that indicate which tables contain the fields listed in the SELECT clause. The FROM clause must always list at least one table. This FROM clause lists the table identifiers products

Union All this is a union keyword as well as an optional all keyword. The Union indicates that the results of the SELECT statements before and after the Union are merged together.

When the all keyword is used, the merge set generated by Union does not delete duplicate rows. This allows Access to significantly improve the performance of the query without checking the results of duplicate rows. The all keyword should be used if any of the following conditions are true:

Are you sure the Select query will not generate any duplicate rows

Whether duplicate rows exist in the result does not matter

You want to see duplicate rows

In this example, we use the ALL keyword because we do not expect to return duplicate rows, nor do they need to be omitted

Select name, Price, guarantee_available, Exclusive_offer This is the second SELECT clause, which is used to introduce the second SELECT statement in the Union query. When writing a federated query, the fields in each SELECT statement must correspond to each other, which means that each SELECT statement must have the same number of fields, and the fields that share common data must appear in the same order in the clause, and the fields must have the same or compatible data type, as shown in the example. Only these fields correspond to each other in order to merge them together in the query output.

Note the name of the field in the Federated query output is extracted from the first SELECT clause. Therefore, in the query output for this example, the data from the field "Warranty_available" and "Guarantee_available" will be named "Warranty_available"

From Services This is the second FROM clause, which completes the second SELECT statement in the union query. Unlike a field in the SELECT clause, a UNION query has no table restrictions on the FROM clause. You can either create a federated query that uses the same table in each FROM clause, or you can use a different number of tables in the FROM clause. In our example, each FROM clause has only one table