How to Create Union Queries to Get Combined Results in MS Access

This article addresses the importance and the use of Union Queries and also gives steps to effectively use it to get combined results.

Suppose a user has an MS Access database with two unrelated tables, one with client data and another with customer data. We assume that both these tables will have a field for contact information. Now if the user wants to see contact information in one view of both these tables, he will have to use the Union Query.

Union Query is used for selecting and retrieving information from more than one table in a single go. To do this, the user needs to select the query from each table, which will retrieve the data and then combine the result, by creating the union query.

Things to remember while using the Union Query

• Union Queries are SQL-specified, so they should be written directly in MS SQL, by switching to SQL view.
• The select queries that the user wants to combine should have the same number of fields, in the same order, comprised of a compatible data types.

Steps to Create Select Queries

• Step 1: Go to Create Tab in Queries group and open Query Design.
• Step 2: Select the Table with the fields that you want to add from the Show Table. The table will be automatically added on your Query Design Window.
• Step 3: Close Show Table dialog box.
• Step 4: Now select the fields, from the Query Design Window.

Ensure to select the same order and number of fields that you are adding to the other select query. Also, check whether the data types in each field are compatible with each other or not.

• Step 5: Now add the criteria in the field with the appropriate expressions.
• Step 6: Run the select query, and recheck its output. Open Design tab, from Results group, and click Run.
• Step 7: Switch to Design View.
• Step 8: Now save your select query, without closing
• Step 9: Repeat the procedure for each select query that the user wants to combine.

Steps to Combine Select Queries

Access displays the SQL view object tab while hiding Query Design Window. And at this time the SQL view Tab is empty.

• Step 1: Select the tab of first select query that the user wants to combine as a union query.
• Step 2: Go to Home tab, Click on View > SQL View.
• Step 3: Copy and insert the SQL statement for select query. Now open the tab, which you created in the above steps for ‘Creating a union Query’.
• Step 4: Paste the SQL statement in SQL View Object in the union query.
• Step 5: Delete the semicolon (;) placed at the end of a select query in its SQL statement.
• Step 6: Press Enter so that the cursor moves down one line, then type UNION in the new line.
• Step 7: Select the tab for next query that the user wants to combine in Union Query.
• Step 8: Repeat all the steps till 5, until the user has copied and pasted all the SQL statements for select query in Union Query’s SQL View Window. Don’t delete the semicolon from the last SQL statement or query.
• Step 9: Go to Design tab, in Results group, then click Run.

The results of the user’s query will automatically appear on your screen in a Datasheet view.

Despite an array of features rolled into its latest versions, the MS Access software still suffers from database crashes. Hence business users must invest in an Access repair utility to deal with contingencies.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com