Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

Union Query

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 duplication of information. Instead we can combine the data with the help of a Union Query and use that as source for our further processing tasks. The source data files will remain physically separate, branch locations can continue updating information on them.

We cannot place Tables or Queries on the Query Design surface, as we do with other type 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 and with little practice you can write them. First have a look at the sample Tables given below, their contents, number of fields in both tables, Field Names, Values, and Field Types etc.

Table-1 Image:

Table-2 Image:

Let us look into an example. The contents of the above tables we will combine 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 string into the SQL design window and save the Query with the name UNION1 or with any name you prefer. Open the Union Query in normal datasheet view.

The output of the above query is given below:

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 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.

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.

There are certain rules that will not be overlooked by the Union Query, like all Source Tables or Queries should have 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 table or query, 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 the Table Branch2 are placed under the Salary and Address fields.

We will make little change on 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 the Table Branch2 in 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 on 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 error when you attempt to view the output.

If you open the 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 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 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.

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 recordset at least once to ensure that they are in order. Once we are sure that the output is appearing correctly then you can apply sorting, 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

Trending

Search

Infolinks Text Ads

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Activity Dates and Quarterly Reports

There are four Quarters in a Year: Jan - Mar = 1st Quarter Apr - Jun = 2nd Jul - Sep = 3rd Oct - Dec = 4th First three months of the yea...

Labels

Blog Archive

Recent Posts