Use UNION Queries to Combine Dissimilar Data into Single Fields

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

FOR THE RECORD

Use UNION Queries to Combine Dissimilar Data into Single Fields

by Sean Kavanagh
Application: Access 97/2000
Operating System: Windows

I've set up a database that keeps track of loans that have been written off due to bankruptcies, etc. I have established three tables, one for personal data, one for the loan information that has been written off, and one for payments that may be received as ordered by bankruptcy court. Each person may have several loans, so I have a one-to-many join between the personal data and loans. Each loan will have multiple payments and therefore also has a one-to-many relationship.

I'd like to print a transaction activity report to provide the regulators and auditors with details of the loans written off and the payments based on the date of the transaction. For instance, if I have the data

John Doe	Loan written off Payments Payments	$10,000 $200 $200	02/15/01 02/28/01 03/31/01
Jane Poe	Loan written off Payments	$5,000 $100	03/15/01 04/28/01

the loans written off totaling $15,000 are in a loans charged-off table, and the payments are in a separate payments table which are linked by loan ID. Running reports by month makes the job easier on the auditors. The report for February should look like this:

Date 2/15/01 2/28/01	Name John Doe John Doe	Write-off $10,000 $200	Payments      $200
Totals		$10,000	$200

March's report should look like:

Date 3/15/01 3/31/01	Name Jane Poe John Doe	Write-off $5,000	Payments      $200
Totals		$5,000	$200

and the report for April would only show the payment of $100.

My problem is that the dates are in separate tables. I could show this data on two separate reports, but the preferred method is combined. How can I combine the two tables so that the report shows the data from the appropriate tables depending upon the date?

Holly Olson
Simpsonville, S.C.

This problem presents us with a perfect opportunity to use a UNION query. A UNION query is a special SQL query that lets you consolidate fields from multiple tables or queries based on the order of the fields, allowing you to combine dissimilar source data into a single field. For instance, in this example, there are two tables that record information by date--amounts of loans written off and amounts of individual payments. Even though the content stored in the two tables is completely different, we can use a UNION query to combine the two table fields containing date data into a single field and the currency data into another one, allowing us to easily create the desired report format.

A query that uses the UNION operator isn't one of the default Access query types, so you can't build one using the query design grid. It's a SQL-specific type of query that requires you to manually write your SQL statement. When you combine data between tables with a UNION query, the fields are matched solely by their corresponding positions within the SQL statement--the actual field names have no bearing on how data is consolidated.

The UNION operator is used in the form:

  <xmp>
SELECT query
UNION
SELECT query
</xmp>

Since data is combined by position, each SELECT query must contain the same number of fields. Also, with the exception of the Number and Text fields, the fields being combined must be of the same data type.

Set up the sample tables

To illustrate how to use UNION queries, we'll create the previously described report. The first thing we need to do is set up some sample data to work with. For our example, we'll create three tables that store contact, loan and payment information. Using the specifications shown in Table A, set up the three tables. Where appropriate, make the AutoNumber field the primary key for the table. Once you've created the tables, enter the data shown in Figure A.

Table A:  Sample table details

Table	Field	Data Type
tblPerson	PersonID FirstName LastName	AutoNumber Text Text
tblLoanWriteOff	LoanID PersonID DateWriteOff AmountLoan	AutoNumber Number Date/Time Currency
tblPayments	PaymentID LoanID DatePaid AmountPaid	AutoNumber Number Date/Time Currency

Figure A:  We'll combine the DateWriteOff and DatePaid data into one field and the AmountLoan and AmountPaid information into another.

Create the report's data source

Using the UNION operator, we'll combine the similar data type information from tblPayments and tblLoanWriteOff. However, viewing the data out of context of its original table makes it impossible to tell what the currency amounts and dates refer to. To overcome this problem, we'll use query expressions to identify whether we're looking at loan or payment information.

To create our report's record source, we'll need a minimum of two queries--the UNION query and a parameter query that filters the results by date timeframes. The data we need for the UNION query can be extracted using solely SQL. However, doing so can be confusing, so we'll look at two ways of creating the UNION query. First, we'll create two standard SELECT queries using the query design grid, which will simply extract the relevant report data from the tables and add source table identifiers. These two queries will form the source data for our UNION query. After we examine creating the query in this fashion, we'll examine how to create it using only SQL.

To start, we'll create a query for the loan write-offs. To do so, switch to the Queries sheet in the Database window and click the New button. When the New Query dialog box appears, double-click on Design View. Add both tblLoanWriteOff and tblPerson to the upper-pane of the design grid and close the Show Table dialog box. Then, join the two tables by dragging the PersonID field from the tblLoanWriteOff box to the PersonID field in the tblPersons box.

Using Figure B as a guide, add the appropriate fields and expressions, then save the query as qryWriteOff. As you can see, we created an expression named Flag to identify that the data returned by this query regards write-off information. We created aliases for DateWriteOff and AmountLoan to make the returned field names more generic once this data is combined with payment content.

Figure B:  We'll use aliases to make the combined data appear more generic.

Close the query at this point and create a new one in Design view. This new query will return similar results for payment information. Add all three tables to the query. Although we won't return data from tblLoanWriteOff information, it's necessary to include this table so that we can associate the payment data with the correct person. First, drag the PersonID field from tblPerson to the associated tblLoanWriteOff field. Then, drag the LoanID field from tblLoanWriteOff to the appropriate tblPayments field.

Now that the relationships are properly set up, create your query based on the example shown in Figure C and save it as qryPayments. The Flag expression again indicates the type of data returned. However, note that we didn't include aliases this time. That's because when you create a UNION query the names stored in your first data source are used when data is returned. Since data isn't consolidated by name, the names in the second query are irrelevant.

Figure C:  The Flag expression will let us keep track of the data's original source.

If you were to view the two queries we've created, you'd get the results shown in Figure D. At this point, we're ready to create our UNION query, which will combine these results into a single recordset. To do so, close the query, open a new query in Design view and immediately dismiss the Show Table dialog box without adding any tables. Since UNION queries are SQL-specific, we can't use the query design grid. Instead, choose Query | SQL Specific | Union from the menu bar so that you can manually enter the SQL statement. When the query window opens, enter

  <xmp>
SELECT * FROM qryWriteOff
UNION 
SELECT * FROM qryPayments
ORDER BY Date;
</xmp>

Figure D:  The next step is to combine these two queries.

At this point, save the query as qryUnion and switch to Datasheet view. As you can see in Figure E, the data has been properly combined and flagged. As we mentioned earlier, this UNION query could have been constructed strictly using SQL. If you want to exclusively use SQL in your UNION query, switch to SQL view and replace the existing statement with the one shown in Listing A. Viewing the results in Datasheet view, you'll find that you receive the same result, albeit with a much more complex query statement. Notice that we created our field aliases using the AS keyword.

Listing A:  SQL statement for our UNION query

  <xmp>
SELECT FirstName, LastName, 
DateWriteOff AS [Date], 
AmountLoan AS [Amount], 
"Write-Off" AS [Flag]
FROM tblPerson 
INNER JOIN tblLoanWriteOff ON 
tblPerson.PersonID = tblLoanWriteOff.PersonID

UNION 

SELECT FirstName, LastName, 
DatePaid, AmountPaid, 
"Payment" AS [Flag]
FROM tblPerson 
INNER JOIN (tblLoanWriteOff INNER JOIN tblPayments ON
tblLoanWriteOff.LoanID = tblPayments.LoanID) ON
tblPerson.PersonID = tblLoanWriteOff.PersonID
ORDER BY Date;
</xmp>

Figure E:  Our UNION query successfully combined the corresponding fields and sorted the results by date.

The last query we'll create is the parameter query that will act as the report's record source. Close qryUnion and select it in the Database window. Then, select Query from the New Object button's dropdown menu. When the New Query dialog box appears, double-click on Design View. Drag the asterisk from the qryUnion box down to the design grid, then drag the Date field down. Next, click in the Criteria row beneath the Date field and enter

  <xmp>
Between [StartDate] And [EndDate]
</xmp>

Finally, clear the Show check box in the Date column, save the query as qryRecordSource, and close the query.

Create the report

To set up your report, select qryRecordSource in the Database window, choose Report from the New Object button's dropdown menu and click OK. Then, select View | Report Header/Footer from the menu bar. Using Figure F as a guide, resize the report and add the appropriate label and text box controls.

Figure F:  Use this report layout to display the consolidated data.

We've seen how to combine data, creating a convenient record source for our report. However, we're now faced with another challenge--displaying the currency amount in the proper report column. To do so, we'll use IIf() functions to conditionally display the Amount value or a zero-length string, based on how the Flag field identifies the data. Since the data returned by the IIf() function will be a string, we'll apply the Val() function when we sum the results. Use Table B to set the properties for the unbound controls, moving from left to right in the report's Detail and Report Footer sections.

Table B:  Report control properties

Control	Property	Value
Date text box	Control Source	Date
Name text box	Control Source	=[firstName] & " " & [LastName]
Write-Off text box	Name Control Source	txtWriteOff =IIf([flag]="Write-Off",[Amount],"")
Subtotal 1	Name Control Source Running Sum Visible	subWriteOff =Val([txtWriteOff]) Over Group No
Subtotal 2	Name Control Source Running Sum Visible	subPayments =Val([txtPayments]) Over Group No
Payments text box	Name Control Source	txtPayments =IIf([flag]="Payment",[Amount],"")
Write-Off total	Control Source	=[subWriteOff]
Payments total	Control Source	=[subPayments]

Finally, save the report as rptMonthStatement and switch to Print Preview. You'll then be prompted to enter the starting and ending dates for the report. Experiment with dates for the individual months of February, March and April. You'll find that the report returns data grouped and formatted as we intended, as shown in Figure G.

Figure G:  Using IIf functions, we can conditionally display which column the amount data appears in.

Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.