Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Union Query

Introduction.

Union Queries are useful for combining data from more than one Table or from other Queries while maintaining the original data physically separate.

Assume that the data Tables from different branches (branch1, branch2 & branch3) are linked directly into our main system in separate files and our job is to combine the contents and prepare monthly Reports, Charts, etc. We can use Append Queries and combine the data into a separate Table and prepare what we want. Then that is a duplication of information. Instead, we can combine the data with the help of a Union Query and use that as the source for our further processing tasks. The source data files will remain physically separate, and branch locations can continue updating information on them.

We cannot place Tables or Queries on the Query Design surface, as we do with other types of Queries instead we have to write the SQL String manually on the SQL Window directly. The SQL Syntax is not that hard to memorize. You need only a little practice to write them. First, let us have a look at the sample Tables given below, their contents, the number of fields in both tables, Field Names, Values, and Field Types.

Sample Tables

Table-1 Image:


Table-2 Image:


Creating a Union Query

Let us look into an example. The contents of the above tables can be combined together with the help of a Union Query.

  1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design View, and then click OK.
  3. Without adding tables or queries, click Close in the Show Table dialog box.
  4. On the Query menu, point to SQL Specific, and then click Union.
  5. Enter SQL SELECT statements to combine data from tables or queries.

Example:

TABLE [Branch1] UNION SELECT * FROM [Branch2];

OR

SELECT * FROM [Branchl] UNION SELECT * FROM [Branch2];

Copy and paste one of the above SQL strings into the SQL design window and save the Query with the name UNION1 or with any name you prefer. Open the Union Query in the normal datasheet view.

The output of the above query is given below:


What Does the Union Query Do?

The above statement will combine data from both Branchl and Branch2 Tables and sort the data on the first field. Any duplicate records found in them will be suppressed from the output. If you have more than 2 Tables or Queries you can repeat the part UNION SELECT * FROM [xxxxxxxx] (where [xxxxxxxx] stands for table or query name) for each table or query and place a semi-colon (;) at the end.

We can interpret the action that goes into the above SQL statement and read it as "Take all the field values of all the records from the table Branchl, select all the field values from all the records from Branch2 and combine them together, remove duplicates out of the resultant recordset and sort the data in ascending order based on values in the first column."

You cannot use field types MEMO, OLE, or Hyperlink in the Source tables or Queries in the above example. But if you add the magic word ALL after the word UNION then all the fields (including Memo, OLE & Hyperlink) are taken in, no sorting is carried out, duplicates are not removed and all the records from all Source Tables or Queries will be given back to you in the order the Tables or Queries are appearing in your SQL Statement.

An Experiment.

Let us try another trick. We will take the first Table contents twice, instead of joining another table, and let us see the difference. Modify the SQL String as follows or create a new Query with the following statement:

TABLE Branch1 UNION SELECT *FROM Branch1;

Open the Query in Datasheet View. What happened? The second set of data vanished. Union Query automatically suppresses duplicates from its outputs unless we explicitly state otherwise. Modify the SQL String by adding our magic word ALL immediately after the word UNION and then open the Query in Datasheet view. Now the Table contents will appear twice in the output.

Points to Note.,

There are certain rules that will not be overlooked by the Union Query, like all Source Tables or Queries should have an equal number of fields, sorting Field Name(s) must be from the first Table or Query, etc., and there is no magic word to over-rule this.

If you mixed the order of the fields and different data types (Number, Text) in different positions the output will be given to you in the order of the fields you have provided and the Column headings will be taken from the First Table or Query only. If you made a mistake in placing the field order differently, in any of the source tables or queries, you will come to know about it only when you take the time to look through the output.  

If you look at the sample output given above, the Age and Country field values from Table Branch2 are placed under the Salary and Address fields.

We will make little change in the structure of the second Table Branch2 and see how the output behaves after that, to understand what can go wrong if we are not careful when handling this type of Queries.

  1. Open Table Branch2 in the design view.
  2. Click and drag the field Age and place it after the field FirstName.
  3. Click and drag the Country field and place it next to the field Age.
  4. Save the Table Structure.
  5. Open the Union Query and inspect the output.

The shifted field values will appear exactly on the new location moving other values forward up to the point from where we have removed the Age and Country fields to the left, ignoring the data types of other member fields in the Union Query and you will not get any warnings.

In the above example, we have taken all the fields from the member tables by giving the * symbol after the SELECT clause. If you need only selected fields from the member tables or queries you must specify them in the correct order.

To select EmployeelD, FirstName, and Birth Date from both tables and to get the output sorted in the FirstName field we can write:

SELECT [EmployeelD],
 [FirstName],
 [BirthDate]FROM [Branch1]
UNION ALL SELECT [ID],
 [FirstName],
 [BirthDate] 
FROM [Branch2]
ORDER BY [FirstName];

The ORDER BY Clause will take Field Names only from the Source Object appearing in the first SELECT statement, otherwise, it will show an error when you attempt to view the output.

If you open Table Branch2 in Datasheet view, you can select and drag Age and Address Columns to any position for your viewing purposes. If you have opted to save the layout, after these changes you will always see the field values in the changed position in the datasheet view, irrespective of the physical position of these fields in the Table structure or Query Design. 

Remember, never depend on the datasheet view of the table or query to determine the field layouts for the Union Query. Always look for the field order in the table structure or Select Query Design View before adding them into the Union Query in the same order.

CAUTION.

When you are designing Union Queries having hundreds or thousands of records in the output, always make a point to check through the object level record set, at least once, to ensure that they are in the correct order. Once we are sure that the output is appearing correctly, then you can apply to sort, suppress duplicates or include conditions to filter data.

When applying conditions you must specify Criteria at each Table or Query level.

For example: if you want to select employees born on 31st December 1960 or earlier from both tables then you will write the SQL in the following manner:

SELECT * from [Branch1]
WHERE ([BirthDate] <=#12/31/1960#)
UNION ALL SELECT * from [Branch2] 
WHERE ([BirthDate] <=#12/31/1960#);

This will take records from each member table or query that satisfies the criteria. Remove the WHERE clause from any one position and check the output again.

Share:

6 comments:

  1. This is helpful information. How would you use this query when there are hundreds of tables that need to be merged into one?

    ReplyDelete
  2. Name the Tables in such a way that we can refer them in code easily. I will give you an example involving some SELECT Queries so that it will be more clear than explaining at length.

    We have a P & L System and the User is asked to set a Parameter Screen where she will enter the Month to prepare the Report for the period she wants.

    All twelve months Select Queries are prepared with the name like CUR_MTH1 to CUR_MTH12 (CUR stands for Current, we have Last-Year and Budget Figures created in similar way) and based on the Month selection we have to assemble the SELECT Queries from Jan to selected Month into a Union Query (CUR_UNION) before processing.

    Part of the Code used for this purpose is given below:

    Private Function DefineUnion()

    Dim sql0 As String, i As Integer, vI As Integer, sql1 As String
    Dim sql As String, db As Database, QryDef As QueryDef

    Me.Refresh

    vI = Me![xMonth] 'read Month Param Setting
    sql0 = "SELECT CUR_MTH1.* FROM CUR_MTH1" 'select January as default

    sql1 = ""
    'select Queries to assemble
    For i = 2 To vI
    sql1 = sql1 & Chr(13) & Chr(10) _
    & " UNION ALL SELECT CUR_MTH" & i & ".* FROM CUR_MTH" & i
    Next

    sql = sql0 & sql1 & " ORDER BY FR, BRA,MTH,CAT;"

    'redefine Union Query
    Set db = CurrentDb
    Set QryDef = db.QueryDefs("CUR_UNION")
    QryDef.sql = sql

    Set QryDef = Nothing
    Set db = Nothing


    End Function

    The SQL of the Output Union Query is given below:

    SELECT CUR_MTH1.* FROM CUR_MTH1
    UNION ALL SELECT CUR_MTH2.* FROM CUR_MTH2
    UNION ALL SELECT CUR_MTH3.* FROM CUR_MTH3
    ORDER BY FR, BRA, MTH, CAT;

    In your case the Source is Tables rather than SELECT Queries. The same method can be applied.

    Regards,

    ReplyDelete
  3. Nice brief and this post helped me alot in my college assignement. Thanks you seeking your information.

    ReplyDelete
  4. Computers...

    We are a group of volunteers and opening a new scheme in our community. Your website provided us with valuable info to work on. You have done an impressive job and our entire community will be thankful to you....

    ReplyDelete
  5. [...] Union Queries read the following article to avoid unexpected problems creeping into the result: Union Query __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]

    ReplyDelete
  6. [...] 11) UNION ja JOIN: http://msaccesstips.com/2008/02/union-query/ [...]

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code