A union data set combines the results returned by two or more data sets. Creating a union data set is similar to using a SQL UNION ALL statement, which combines the result sets of two or more SELECT statements into a single result set.
Create a union data set to consolidate data from multiple sources that have similar data structures. For example, a company maintains separate database tables to store contact information about employees and contractors. The structure of the tables are similar. Both contain Name and Phone fields. Suppose you want to create a master contact list for all employees and contractors. The solution is to create one data set to retrieve employee data, a second data set to retrieve contractor data, and a union data set that combines data from the previous data sets.
Figure 13-1 illustrates the data sets that return employee and contractor data.
|
Figure 13-1
|
When creating a union data set, you select the fields to include. Figure 13-2 shows a union data set that includes all the fields from both Employees and Contractors data sets. The Name field contains all employee and contractor names. The Phone field also contains all employee and contractor phone numbers. The E-mail field exists only in the employee data set, so only employee data rows have e-mail data.
|
Figure 13-2
|
Union data set that combines all data from Employees data set and Contractors data set
|
Figure 13-3 shows a union data set that includes only the common fields, Name and Phone, from the Employees and Contractors data sets.
|
Figure 13-3
|
Union data set that combines data from common fields in Employees data set and Contractors data set
|
In the previous example, the two data sets used to create a union data set contained common fields with the same names. This condition is required for consolidating data into a single field. However, data sources often use different field names.
Suppose the Name field in the Employees and Contractors tables is EmployeeName and ContractorName, respectively. To create a union data set that consolidates employee and contractor names in a single field, rename the field names in the individual data sets to use the same name. When creating the Employees data set, in Output Columns, use the Alias property to give the EmployeeName field another name. Figure 13-4 shows the EmployeeName field with the alias, Name.
|
Figure 13-4
|
Similarly, when creating the Contractors data set, edit the ContractorName field to use the same alias.
