InfoAccess - Combining Data from Multiple Queries Into One Result Set
This document explains what steps to take in order to combine the results from multiple queries into a single file. This is useful in cases where you cannot obtain all the data that you need with a single query.
An example of this would be if you wanted a list of all students who graduated in ‘X terms’ (Retention data view) AND students who are planning on graduating in ‘Y terms’ (current student data view) and need to put both result sets in a single file for a mass mailing.
To Combine Data from Multiple Queries Into One Result Set:
- Create and process a query to select students who have already graduated.
- Insert a new query, selecting students who are planning on graduating.
- Make sure you have retrieved the same unique field(s) (i.e., Emplid) so you can use it to join both results sets..
You now have two queries listed in the Catalogue panel
- Now, insert a third query. Interactive Reporting will ask if you want to connect with an existing connection, choose “NO.”
- A new window will pop up allowing you to choose a connection. Choose “No Connection” as the third query will use local results for processing.
- Open up Local Results (right-click on Tables) and add both results sections to your Contents Pane.
- Join the appropriate field(s) from the two tables (in this case, EmplID), and build the rest of your query. The records in each results set will not match, so make the join type an Outer join (left click/right click/properties) — This will retrieve all records whether the EmplIDs match or not. Now process this query.
- You should now have three queries listed in your catalogue panel
- In the third results section, create computed fields to ‘add’ the like fields from the tables. This accomplished using NVL (Functions – Conditional – NVL)
- Look at the new computed field and you will see all the values in the two name fields listed in one column. To eliminate confusion, ‘hide’ the original two columns. Continue this process with the rest of your fields.
Found on the UW Milwaukee website - Submitted by MaryAnn Riggs 08/20/03