I have been so theoretical since the introduction of this
SQL chapter for MS Access and MySQL Databases. Now let me show you the real life or practical, real life and business applications of SQL (Structured Query Language) that I have been explaining. I will apply SQL to build these two types of
advanced query in MS Access and MySQL databases, namely:
1. THE UNION QUERY
2. THE QUERY JOIN
THE UNION QUERY:
This just like the table relationship/join I explained in chapter 2. This query lets you combine
the results returned by two SELECT statements or queries and displays them as
one result. In table join, MS Access does all the underground work for you. All
you just did then was to click and Access generates the SQL statements without
your knowledge. But now, you will generate everything from the scratch. That is
what makes it an advanced query.
This is part 3 of chapter 6 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain the real life or business application of SQL in Relational Database Management Systems (RDBMS) software like Microsoft Access 2016, 2013 and other lower versions.
NOTES:
The UNION query joins two or more SELECT queries together,
also, the SELECT statements that you combine in a union query must have the
same number of output fields in the same order and with the same or compatible
data types.
Also note that the Text
data type and the Number data
type are compatible for the purpose of union query.
You have to switch to the SQL view in MS Access to create a
union query because a union query is SQL –specific.
The basic SQL syntax for a union query that combines two
SELECT query is:
SELECT [field_1],
[field_2],… FROM [table_1], [Table_2],…
UNION ALL
SELECT [field_a],
[field_b],… FROM [table_a], [Table_b],…
NOTE:
The ALL keyword is optional. When you use the ALL keyword,
duplicate rows are not removed from the combined set that is produced by the
UNION query. You can also use it when you are sure that the SELECT statements
will not produce any duplicate rows or if you want to see duplicate rows.
The UNION keyword combines the results returned by two
SELECT statements or queries and displays them as one result.
HOW TO CREATE A UNION
QUERY IN MS ACCESS
There are two basic methods of creating UNION queries in
Microsoft Access:
1. Combining two SELECT queries previously created in Query Design
view
2. Creating the UNION query directly in SQL view
COMBINING TWO SELECT
QUERIES PREVIOUSLY CREATED IN QUERY DESIGN VIEW
This method becomes the alternative if you have previously
created the two SELECT queries and then wish to combine or merge their results.
For example, as an Administrative Head of a University, you
might want to merge or join two queries named old students record and new
students record to form one query. Since you already have the two queries
available, you will use this alternative to create the JOIN query.
To Combine Two
Previously Created SELECT Queries Using the UNION Keyword:
1. Open the first SELECT query in SQL view and copy the SQL
statement. Referring to the above example, I will first open the new students records SELECT query in
SQL view and copy the SQL statement as shown below.
2. Click Query Design located
in the Queries group under the Create tab.
3. This prompts the Show
Table dialogue box. Close the dialogue box without selecting any query by
clicking the Close button.
4. Click the UNION icon
located in the Query Type group under
the Design tab as shown below.
5. From the screenshot above, you will notice that the Query Design view option is no more available when you click the View drop down arrow. You only see two
options: the Datasheet View and the SQL view. Also, the SQL view object tab is displayed but
empty.
6. Now, paste the initially copied SQL statement for the first
SELECT query into the SQL view object window of the UNION query as shown below.
Delete the semi-colon at the end of the SELECT statement and press the Enter
key to move the cursor down to a new line as shown below.
7. Type UNION.
Optionally, you can type a space, followed by the ALL keyword. Then press the Enter key again to move your cursor to
another new line.
8. Open the second SELECT query in SQL view and copy the SQL
statement. So referring to the above example, I will open the old students records SELECT query in
SQL view and copy the SQL statement.
9. With
your cursor at the new line of in the SQL view object tab of the UNION query,
paste the copy of the SQL statement of the second select query as shown below.
NOTE:
To add an additional SELECT query statement to the above
UNION query, repeat steps 7 to 9 above.
10. Click the Run! icon
located beside the View drop down menu in the Results
group under the Design tab. The
result of your query appears in Datasheet
view. Save the UNION query with a unique name. The result of the above
example is shown below.
CREATING THE UNION
QUERY DIRECTLY IN SQL VIEW
This method is the best alternative if you have not previously
created the two SELECT queries. So you now type the whole SQL statement for the
UNION query directly in the SQL view without having to copy and paste as you
did in the first alternative above.
To Create the UNION
Query Directly in SQL View:
1. Click Query Design located
in the Queries group under the Create tab.
2. This prompts the Show
Table dialogue box. Close the dialogue box without selecting any query by
clicking the Close button.
3. Click the UNION icon
located in the Query Type group
under the Design tab. You will
notice that the Query Design view option is no more available when
you click the View drop down arrow.
You only see two options: the Datasheet
View and the SQL view. Also, the
SQL view object tab is displayed but
empty.
4. In the SQL view object window, type SELECT, followed by a list of the fields from the first table or
set of tables that contain fields you want to include in the UNION query. Use a
comma and a pace to separate the field names.
Your SELECT clause should take this format:
SELECT
table1.[field1], table1.[fied2],…, table2.[field1], table2.[field2],…
Then press the Enter key when you are through to create a
new line.
5. Type FROM, followed by the names of the tables that contain the fields listed in the preceding SELECT clause, separating the table names with a comma and a space.
Your WHRE clause should follow this format:
FROM [table1], [table2],…
Then press the Enter key to create a new line.
6, To specify a criterion for a field from one of the tables listed in the preceding FROM clause, type WHERE, followed by the field name, a comparison operator (usually an equality sign (=)) and the criterion. You can add additional criteria to the end of the WHERE clause by using the AND or the OR keyword.
Your WHERE clause should follow this format:
WHERE table1.[field1] = ‘field data 1’ AND table2.[field1] = ‘field data 2’
If you specified criteria, press the Enter key to create a new line.
With this, you have succeeded in creating the first SELECT query statement for the JOIN query. But don’t put a semi-colon at the end of this first SELECT query statement. You will put at the end of the second SELECT query statement (or the last SELECT query statement if you are creating more than two SELECT queries).
7. Type UNION. If you do not want to the query to remove duplicate rows from the output, type a space, followed by the ALL keyword. Then press the Enter key to create a new space.
8. Now you will start creating the second SELECT query statement for the JOIN query. Repeat steps 4 to 6.
9. To include additional SELECT query statement in the JOIN query, type UNION. If you do not want to the query to remove duplicate rows from the output, type a space, followed by
the ALL keyword. Then press the
Enter key to create a new space. Then repeat steps 4 to 6.
10. Type a semi-colon to indicate the end of your query.
11. Click
the Run! icon located beside the View drop down menu in the Results
group under the Design tab. The
result of your query appears in Datasheet
view. Save the UNION query with a unique name.
This is the end of part 3 of chapter 6 of this FREE online MS Access tutorial training series.
Recommended MS Access Textbook
Click Here to know more about the book.
In the next part (part 4), I will explain and illustrate the how to create JOIN queries in MS Access.
Was this tutorial helpful to you?
Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.
Also click Here to subscribe for free so that you will get our latest game updates in your email.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!