LEARN MS-ACCESS TIPS AND TRICKS

Union Query

a.p.r. pillaiFebruary 21, 2008msaccessQuery, Queries 6 comments

Union Query Rules to Remember.

Union Queries are useful for combining data from multiple tables or queries while keeping the original data physically separate.

For example, data tables from different branches (Branch 1, Branch 2, and Branch 3) are linked directly into our main system as separate files. Our task is to combine their contents to prepare monthly reports, charts, and other summaries.

One approach is to use Append Queries to merge all the data into a single table before processing. However, that creates duplication, as we’re storing the same data in two places. A better option is to use a Union Query, which combines the data virtually, without altering or duplicating the source tables. This way, branch locations can continue to update their own files independently, while our combined view remains up to date.

Unlike other queries, we cannot drag and drop tables or queries onto the Query Design grid when creating a Union Query. Instead, we must write the SQL statement manually in the SQL View. Fortunately, the syntax is straightforward—you just need a little practice to get comfortable with it.

Before we proceed, let’s look at some sample tables, including their contents, number of fields, field names, values, and data types.

Sample Tables.

Table 1 Image:

Click to Enlarge

Table 2 Image:

Click to Enlarge

Creating a Union Query.

Let us look at an example. The contents of the above tables can be combined in a Union Query.

In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
In the New Query dialog box, click Design View, and then click OK.
Without adding tables or queries, click Close in the Show Table dialog box.
On the Query menu, point to SQL Specific, and then click Union.
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:

Click to Enlarge

What Does the Union Query Do?

The above SQL statement combines data from both the Branch1 and Branch2 tables, sorting the results by the first field. Any duplicate records found in the combined data will be removed from the output.

If you have more than two tables or queries, you can extend the statement by repeating the part:

sql
UNION SELECT * FROM [xxxxxxxx]

Here, [xxxxxxxx] represents the name of the additional table or query. Continue adding these parts for each source, and place a semicolon (;) at the very end of the statement.

In simple terms, the SQL statement can be interpreted as follows:

Take all field values from all records in the Branch1 table, then take all field values from all records in the Branch2 table. Combine the results, remove any duplicates, and sort the records in ascending order based on the values in the first column.

Note that you cannot use fields with the Memo, OLE, or Hyperlink data types in the source tables or queries for this type of Union Query.

However, if you insert the keyword ALL immediately after the word UNION, the behavior changes:

All field types (including Memo, OLE, and Hyperlink) are allowed.
No sorting is performed.
Duplicate records are retained.
The records are returned in the order in which the source tables or queries are listed in your SQL statement.

An Experiment.

Let’s try another experiment. Instead of joining a different table, we will use the table contents twice and observe the result. Modify the SQL string as follows, or create a new query with this statement:

sql
SELECT * FROM Branch1  
UNION  
SELECT * FROM Branch1;

Open the query in Datasheet View. What do you see? The second set of records is gone. That’s because a Union Query automatically removes duplicate records from its output unless told otherwise.

Now, modify the SQL string by adding our magic word ALL immediately after the word UNION:

sql
SELECT * FROM Branch1  
UNION ALL  
SELECT * FROM Branch1;

Open the query again in Datasheet View. This time, the table contents will appear twice in the output because duplicates are no longer suppressed.

Points to Note:

A Union Query has certain rules that cannot be bypassed. For example:

All source tables or queries must have the same number of fields.
Any sorting must be done using field names from the first table or query in the statement.

These requirements are built into how Union Queries work—there is no magic word or keyword that can override them.

If the fields in your source tables or queries are arranged in a different order, or if the data types in corresponding positions differ (for example, Number in one table and Text in the other), the Union Query will still return the results in the order you specified. However, the column headings will always be taken from the first table or query in the statement. If the field order is incorrect in any of the sources, you may not notice it immediately—the mismatch often becomes apparent only when you carefully review the output.

If you look at the sample output above, you’ll notice that the Age and Country values from Branch2 are appearing under the Salary and Address columns.

To see just how easily this kind of mismatch can happen, let’s make a small change to the structure of the second table (Branch2) and observe the output again. This will help us understand what can go wrong when we’re not careful in handling a Union Query.

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

The shifted field values will appear exactly in their new positions, moving other values forward from the point where we removed the Age and Country fields to the left position. This happens without considering the data types of the other fields in the Union Query, and no warning messages are displayed.

In our earlier example, we selected all the fields from the member tables by using the * symbol after the SELECT clause. If you only need specific fields from the member tables or queries, you must list them explicitly and in the correct order.

For example, to select EmployeeID, FirstName, and BirthDate from both tables and display the results sorted by 'FirstName', We can write the SQL string as shown below:

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

The 'ORDER BY' Clause can reference only the field names from the source object used in the first SELECT statement; otherwise, an error will occur when you attempt to view the results.

If you open Table Branch2 in Datasheet View, you can select and drag the Age and Address columns to any position for viewing purposes. If you save the layout after making these changes, the columns will always appear in their rearranged positions in Datasheet View, regardless of their actual physical order in the table structure or query design.

Important: Never rely on the Datasheet View of a table or query to determine the field layout for a Union Query. Always refer to the actual table structure or the Select Query Design View to confirm the field order before adding them to the Union Query, and ensure that they appear in the same order in all member queries.

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 listed correctly, then sort, suppress duplicates, or apply conditions to filter the 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.

Percentage on Total Query

a.p.r. pillaiFebruary 15, 2008msaccessQuery, Queries 5 comments

Calculating Line Value Percentage on Total.

To calculate the percentage contribution of each value in a row based on the total of all rows, let’s work through an example.

Assume that Northwind Traders stores customer-wise and order-wise sales figures in a table. Management now wants a customer-wise sales summary and has further requested the following:
Top 10 Customers Only – Display only those customers with the highest sales values.

We can solve this easily with a Total Query.

Steps:

Import the Orders and Customers tables from the Northwind.mdb sample database.
- If you’re unsure where the sample database is stored on your machine, refer to the instructions on the Saving Data on Forms Not in Table page for location details.
Create a Total Query to sum the sales per customer.
Calculate each customer’s percentage by dividing their sales value by the total sales of all customers.
Apply a sort to display results in descending order and limit the output to the Top 10 customers.

With that, you’ll have a clear view of the top customers and their share of the total sales.

The Query's SQL Statement.

For our task, we only need the Orders table. The Customers table serves as the source for a combo box in the Orders table, allowing us to enter the CustomerID. If we omit the Customers table, an error message will appear when we use the Orders table in our query.

To proceed, open the SQL (Query) editing window in your project (without selecting any table or query). Then, copy the following SQL statement and paste it into the SQL editing window:

SELECT TOP 10 Orders.CustomerID,
   Sum(Orders.Freight) AS Sales,
   DSum("freight","orders") AS Total,
   Sum([freight]/DSum("freight","orders")) AS Percentage
FROM Orders
GROUP BY Orders.CustomerID
ORDER BY Sum(Orders.Freight) DESC;

Switch the query from SQL View to Design View (View → Design View) to see the layout visually.
We have grouped the records by CustomerID, summed the Freight values for each customer, and renamed this column Sales.
The third column calculates the Total Freight Value for all records in the Orders table using the DSum() function. Note that in the Total row, we have used Expression instead of Sum.
To calculate the percentage of each customer’s sales against the total sales, we repeat the Sum() and DSum() functions in the formula:
```
css
Percentage: Sum([Freight]) / DSum("Freight", "Orders")
```
Even though we have already calculated Sales (customer-wise total) and Total (overall freight value) in earlier columns, we cannot simply use 'Sales / Total' In this query. Total Queries do not allow referencing calculated field names directly in the same query.
If you find the percentage formula too cumbersome, you can save this query without the Percentage column, then create a second query using the first Query as its source. In the second query, you can simply write:
```
makefile
Percentage: Sales / Total
```
This time, it will work without issue.

Finally, to format the Percentage column, click anywhere in the column, open the Property Sheet (View → Properties), set Format to Percentage, and set Decimal Places to 2.
Listing the Top 10 highest Percentage Records.
To limit the output of the Query to 10 Records with the highest Sales values, we have to change the Top Values Property of the Query. Click on an empty area somewhere near the Table above.
Display the Property Sheet (View -> Properties) if you have closed it.
Change the Property Top Values to the desired value. Currently, it will be 10, because we have mentioned SELECT TOP 10 in the SQL String that you have pasted into the SQL Window.
Open the Query in Normal Datasheet View and check the Result. We have already set the Sorting order to descending under the Sales Column so that the Highest Sales Value and Percentage will be at the top Row.

Earlier Post Link References:

Selection of Office Assistant

a.p.r. pillaiFebruary 08, 2008Animations, msaccess animation No comments

Office Assistant Animation - Access 2003.

Since we’ve already experimented with several Office Assistant options in earlier examples, let’s have a bit of fun this time.

If you’ve landed directly on this page, you might enjoy exploring some of our previous related topics first. The links to those articles are provided below:

Message Box with the Office Assistant
Message Box with Menu Options using the Office Assistant
Message Box with Check Box type Menu with the use of Office Assistant
Command Button Animation (Visit this page to link the Microsoft Office Library File to your Project).

We know that MS Access—and other MS Office applications—allow us to choose our preferred Office Assistant character from the Help menu. In this example, however, we’ll take a different approach. We’ll design an MS Access form that automatically organizes and displays all the Office Assistant characters available on your PC. From this form, you can browse through them, pick your favorite, and set it as the default Office Assistant anytime you wish.

A sample screenshot of the program in action is shown below:

Right-click to open the Large Image in a New Window.

We need a small Table with the Office Assistant's File names in a List Box as shown on the left side of the Form above.

Designing a Table and Form.

I created a Table to save the names of the Office Assistant characters' Files List, with their Path Name.

Right-click to open the Large Image in a New Window.
Out of the eleven items, the first seven files are stored in the same location, while the rest are located elsewhere. Before adding these entries to your table, it’s best to run a search on your PC for all files with the '.ACS' extension and use the results to create your list.
To search for these files:
1. Go to Start → Search → For Files or Folders → All Files and Folders.
2. Enter *.ACS In the search box and click Search.
3. When the search is complete, note down the full path of each file found.
4. Enter these full paths into your table.
Next, design a small form and set the Form and Control properties as given below.
Right-click to open the Large Image in a New Window.
Create a List Box on the Detail Section of the Form. Click on the List Box (if it is not in selected state), display the Property Sheet (View -> Properties), and change the Property Values as shown below:
- Name: AsstList
- Row Source Type: Table/Query
- Row Source: SELECT AssistantID, 'Assistant.asstChar' FROM Assistant;
- Column Count: 2
- Column Heads: No
- Column Widths: 0";0.875"
- Bound Column: 1
- Multi-Select: None
- Left: 0.125"
- Top: 0.3125"
- Width: 1.2083"
- Height: 1.4375"
- Font Weight: Bold
Create a Label control on the top of the List Box and change the Caption Property value to Office Assistant.
Draw a Rectangle Control on the right side of the List Box and change the Property values as given below.
- Left: 1.4271"
- Top: 0.3125"
- Width: 1.7188"
- Height: 1.4375"
- Back Style: Transparent
- Special Effect: Sunken
Create two Command Buttons below the List Box as shown on the Design. Display the Property Sheet of the Left Command Button. Change the following Property values as given below:
- Name: cmdSetAsst
- Caption: Set as Default
Change the property values of the right-side Command Button.
- Name: cmdCancel
- Caption: Cancel
Form Property Values.
Now, to change the Properties of the Form, click on the top left corner of the Form (at the intersection where both the vertical and horizontal Scales meet). To display the Property Sheet, click on the All tab of the Property Sheet, if that is not the current one, and change the Property Values as given below.
- Caption: Office Assistant
- Default View: Single Form
- Scroll Bars: Neither
- Record Selectors: No
- Navigation Buttons: No
- Dividing Lines: No
- Auto Resize: Yes
- Auto Center: Yes
- Pop Up: Yes
- Modal: No
- Border Style: Dialog
- Control Box: Yes
- Min Max Buttons: None
- Close Button: Yes
- What's This Button: No
- Width: 3.3333"
- Allow Design Changes: Design View Only
Click on the Detail Section of the Form. Display the Property Sheet, if you have already closed it, and change the Property:
- Width = 2.4271"
Select Save from the File Menu, and save the Form named Assistant, and stay in Design View; don't close the Form.

The VBA Code.

Display the VBA Module of the Form, View -> Code. Copy and paste the following code into the VBA Module, save, and close the Form:

Dim defaultAssistant As String
Dim strAsst() As String

Private Const m_left As Integer = 500
Private Const m_top As Integer = 225

Private Sub AsstList_Click()
Dim vID As Byte, strFileName, FRM As Form, l As Long
Dim ctrl As Control

On Error GoTo AsstList_Click_Exit:

vID = [AsstList]
With Assistant
    .On = True
    .fileName = strAsst(vID)
    .Animation = msoAnimationGetAttentionMajor
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Left = m_left
    .Top = m_top
    .Visible = True
End With

AsstList_Click_Exit:
Err.Clear
End Sub

Private Sub cmdCancel_Click()
On Error GoTo cmdCancel_Click_Exit
With Assistant
    .On = True
    .fileName = defaultAssistant
    .Animation = msoAnimationBeginSpeaking
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Left = m_left
    .Top = m_top
    .Visible = True
End With
DoCmd.Close acForm, Me.Name

cmdCancel_Click_Exit:
Err.Clear
End Sub

Private Sub cmdSetAsst_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
Dim db As Database, rst As Recordset
Dim acsFiles As Integer, i As Integer

On Error GoTo Form_Load_Exit
defaultAssistant = Assistant.fileName

acsFiles = DCount("* ", "Assistant")
If acsFiles = 0 Then
   MsgBox "Assistants File Not found. "
   Exit Sub
End If
ReDim strAsst(1 To acsFiles) As String
Set db = CurrentDb
Set rst = db.OpenRecordset("Assistant", dbOpenDynaset)
i = 0
Do While Not rst.EOF
i = i + 1
strAsst(i) = rst![Path]
rst.MoveNext
Loop
rst.Close

With Assistant
  If .On = False Then
    .On = True
  End If
    .fileName = defaultAssistant
    .Animation = msoAnimationBeginSpeaking
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Left = m_left
    .Top = m_top
  If .Visible = False Then
    .Visible = True
  End If
End With
Form_Load_Exit:
Err.Clear
End Sub

Selecting the Office Assistant.

Open the Form in Normal View. Click the items in the List Box one by one, and give a few seconds' time gap for the Assistant Character to appear. When your favorite image appears, click on the Set as Default Command Button to set the current Character as the default Office Assistant. If you change your mind, then click the Cancel Button to retain the existing one.

You can export this Form to your other Projects and use it to link the Microsoft Office Library File.

Note: At the beginning of the Code, I have defined two Constants, m_left = 500 and m_top = 225, to position the Image on the Form when it appears based on my machine's Screen resolution 1024 x 768. You may change this value to adjust it to your machine's Screen Resolution.

Download Demo Database

Office Assistant with Check Box Menu

a.p.r. pillaiFebruary 01, 2008Animations, msaccess animation No comments

Check-Box Menu - Office Assistant: Access 2003.

In an earlier article, we explored the Office Assistant for message boxes and option menus.

When designing applications, it’s worth adding features that stand out—something more engaging than the standard command buttons or plain message boxes. While data processing remains the core, repeating the same design elements can become monotonous for both the developer and the user. This is why Windows and Microsoft Office continue to evolve, offering new features and improved interfaces to keep the experience appealing.

Check-box Type Menu.

Next, we’ll experiment with checkbox-style menu options in a message box, displayed through the Office Assistant. We’ll use the same sample form created for the earlier program. An image showing the program in action is provided below.

Right-click to open the Large Image in a New Window.

If you haven’t tried the earlier example, read the article MsgBox with Options Menu and follow the preparation steps outlined there to ensure these programs run without errors.

Open the OptionCheck form (created in the previous example) in Design View.
Add a second Command Button to the form.
Select the new button, open the Property Sheet (View → Properties), and update:
- Name: cmdChk
- Caption: Get Checked
With the form still in Design View, open its VBA module (View → Code).
Copy and paste the code provided below into the module, then save the form.

Private Sub cmdchk_Click()
Dim OptionArray(1 To 5) As String
Dim i As Integer, msg As String, title As String

OptionArray(1) = "Display Report Source Data"
OptionArray(2) = "Print Preview"
OptionArray(3) = "Print"

msg = "Please Select an Option"title = "Report Options"

i = MsgGetChk(msg, title, OptionArray())

If i > 0 Then
    Me![optTxt] = i & " : " & OptionArray(i)
End If

Select Case i
    Case -2
       msg = "Cancelled"
       Me![optTxt] = i & " : Cancelled"
    Case 1
        'DoCmd.OpenForm "myForm", acNormal
        msg = "Display Report Source Data"
    Case 2
        'DoCmd.OpenReport "myReport", acViewPreview
        msg = "Print Preview"
    Case 3
        'DoCmd.OpenReport "myReport", acViewNormal
        msg = "Print"
End Select

        MsgOK "Selected: " & msg
End Sub

The Get Checked routine displays a Check Box–style options menu inside an Office Assistant–based message box. When the command button cmdChk is clicked, the routine calls the MsgGetChk() function, passing the prompt text, dialog title, and an array of available options.

The user can select one or more check-boxes and confirm the choice. The selected items are returned as a string, which is then displayed in:

A message box showing the chosen options.
A text box on the form for reference.

By encapsulating this functionality in MsgGetChk()The same check-box menu can be reused from any form or module by simply passing different text and option arrays.

The Get Checked routine is a modified version of the Balloon-Type Options example used earlier in the form module. The core logic remains the same, but two key adjustments have been made for this demonstration:

Fewer menu options – The OptionArray() variable now holds only three items, as the OK and Cancel buttons are provided by the main program.
Cancel button handling – A Select Case...End Select structure includes a check for the Cancel button. If the user clicks Cancel, the program exits immediately.

In a real-world implementation, you can safely remove Case 2 its following two statements—these are included here only to illustrate the Cancel-handling logic.

The Main VBA Program.

Copy and paste the following main program into a Global VBA Module in your Project and save it.

Public Function MsgGetChk(ByVal strText As String, ByVal strTitle As String, ByRef obj) As Integer
Dim X As Integer, i, c As Integer, k As Integer
Dim Bal As Balloon, vSelect As Integer

On Error GoTo MsgGetChk_Err

Set Bal = Assistant.NewBalloon
i = 0k = UBound(obj)
k = IIf(k > 5, 5, k)

For X = 1 To k
   If Len(obj(X)) > 0 Then
     i = i + 1
   End If
Next

ForceEntry:

With Bal
   .Animation = msoAnimationWritingNotingSomething
   .Icon = msoIconAlert
   .Heading = strTitle
   .Text = strText
   .BalloonType = msoBalloonTypeButtons
   For X = 1 To i
     .Checkboxes(X).Text = obj(X)
   Next
   .Button = msoButtonSetOkCancel
  vSelect = .Show

  If vSelect = -2 Then
        MsgGetChk = vSelect
        Exit Function
  End If

  c = 0
  For X = 1 To i
    If .Checkboxes(X).Checked Then
       MsgGetChk = X 
' get the item checked by the user
       c = c + 1 
'more than one item checked
       If c > 1 Then
          Exit For
       End If
     End If
  Next

  If c > 1 Then
     strText = "Select only one item. "
     GoTo ForceEntry
  End If

  If c = 0 Then
     strText = "Select one of the Options or Click Cancel! "
     GoTo ForceEntry
  End If

End With

Assistant.Visible = False

MsgGetChk_Exit:
Exit Function

MsgGetChk_Err:
MsgBox Err.Description, , "MsgGetChk"
Resume MsgGetChk_Exit
End Function

This main program is largely the same as the Options Menu example from our earlier demonstration, with a few key changes:

1. MsgGetOpt Function

The `MsgGetOpt()` function is responsible for displaying the Office Assistant with a list of options in the form of a menu. It takes an array of option text items, assigns them to labels or checkboxes, and then displays them through the Office Assistant interface. The function waits for the user to make a choice, either by clicking the OK or Cancel Button, and then returns the index of the selected item.

This modular approach allows the same function to be reused with different sets of options, making it versatile for a variety of menu-based interactions in your VBA projects.

2. Get Checked Routine.

The Get Checked routine is adapted from the Balloon Type Options program we created earlier. Two key changes were made for this example:

Reduced option count – The `OptionArray()` variable now holds only three items, as the OK and Cancel buttons are provided in the main program itself.

Cancel handling – A `Select Case` block now checks for the Cancel condition. If the user clicks Cancel, the program exits.

When adapting this method for your own project, you can remove `Case 2` the two lines beneath it. They are included here only to demonstrate handling of the Cancel event.

3. Main Program (OptionCheck Form)

The main program closely follows the structure of our earlier Options Menu example, with the following adjustments:

The assignment statement

vba
labels(X).Text = MaxArray5obj(X)

is replaced by

vba
.Checkboxes(X).Text = obj(X)

Additional logic ensures:
1. Single selection enforcement – If more than one box is checked, the user is prompted to select only one.
2. Mandatory selection – If OK is clicked without checking any option, the user is asked to select one, or click Cancel if they change their mind.

Before running, make sure the Office Assistant is visible (Help → Show the Office Assistant or press Alt+H, then Alt+O).
To choose the Office Cat or another character, right-click the Assistant, select Choose Assistant, and use Next or Back to cycle through the options. The animation type is 'msoAnimationWritingNotingSomething' set in the program, and while it works with all characters, it pairs especially well with the Office Cat.

To test:

Open the OptionCheck form in Normal View.
Click Get Checked.
The Office Assistant will appear with the checkboxes.
Try clicking OK with multiple selections or no selections to see the validation prompts in action.