Access |
 |
by Eddie Mijares |
A union query combines the fields from two tables and presents the result as a single table. The most typical example is combining a Customer and Vendor table for City, State or Zip Code information.
Most developers consider union queries difficult because they cannot be created in the Access Query Design grid. Instead, they must be keyed in as SQL (Structured Query Language) statements. Union queries are really quite easy to construct. They are simply two select queries with the word "Union" between them.
My favorite use of union queries is for combo boxes which should display values from a table and also allow the selection of "All" data. For instance, let us say we want to print a report for any specific status code or all available status codes.
There are three possible solutions to this situation. First, we could add the word "All" to the Status Code Master table, but this would display the word "All" in every combo box and a user could accidentally assign "All" as the status of a record. Second, we could use a function to load the combo boxes which we want to display the word "All". While this solution satisfies our needs it requires a great deal of coding and effort. The last solution would be to use a simple union query to combine the status code data and the word All.
Now let's construct a union query which combines the status codes from the Status Code Master table and the word "All". In order to get a list of status codes we must select the status code field from the Status Code Master table.
In addition, if we would like the status codes to be displayed in alphabetical order, then we should add the order by clause to our SQL statement.
The resulting SQL statement would be:
SELECT [Status Code] FROM [Status Code Master] ORDER BY [Status Code]
Next, we need a SQL select statement which returns the word "<All>". I like to use the less than (<) and greater than (>) symbols because they make the word stand out and also because they make the word sort before any regular alphabetic characters. The SQL statement to add the word "<All>" would be:
Select "<All>" as [Status Code] from [Status Code Master]
Now, all we have to do to make this a union query is to add the word Union between these two SQL statements.
Select "<All>" as [Status Code] from [Status Code Master]
UNION SELECT [Status Code]
FROM [Status Code Master]
ORDER BY [Status Code]
Simply save the SQL statement as a query and it is ready to be used as the basis for a combo box or list box. This technique can be expanded to include tables which contain more than one column by assigning additional values to the other columns as well. For instance, if we would like to display employee names and IDs, we could also add an "<All>" and "99999" for the selection of all employees.
The resulting union query would be:
Select "<All>" as [Employee Name], 99999 as [Employee ID] from [Employee Master]
UNION SELECT DISTINCTROW [Employee Name], [Employee ID]
FROM [Employee Master]
ORDER BY [Employee Name]
Union queries may take some time to get used to, but they are a simple way to add additional functionality to your reporting and inquiry forms. In addition, they will prevent your application from being loaded with fictitious data and large coding modules. Give union queries a try; I am sure you can add them to your tool kit or bag of tricks.
Eddie Mijares, a HAL-PC member, is the president of Major Systems Corp., engaged in MS Access and VB consulting.
E-mail me at webmaster@hal-pc.org with any comments you have and tell me what you want to see here.
Back to the User Journal Home Page