Union Query
Example One
This tip Union Query Example One will step you through one use of the union query.
Question:
I have a table with two fields (columns). The first field contains a unique ordinal number for each row. The second field contains some text.
I've created a query that shows only the rows that have the word "chair" in them (in the second field). Let's say there are only 2 such rows: row 4 and row 9.
How can I create such a query that would also show me the two previous rows and one after? In the example above it would be the following rows: 2, 3, 4, 5, 7, 8, 9, and 10.
Answer:
To do what you ask try the following:
Create a query with the above table, with the ordinal field, and the "chair" filtered field.
In the third column type the following in the first row:
PlusOne: [ID]+1
In the next column, the following:
MinusOne: [ID]-1
Then, finally, in the next column:
MinusTwo: [ID] - 2
Exchange the [ID] field to the name of your ordinal field.
Close and save the query calling it OneBeforeAfter1
Create a new query - but do not follow the wizard as you will be building a Union Select query.
To build a union query:
*****
Microsoft Access 2003 or earlier
Query > New > Design View > Close
(i.e. do not add any tables)
Then, on the toolbar, > Query > SQL specific > Union
A window will open for you to write a SQL query.
Microsoft Access 2007 or later
On the Ribbon > Create > Design View > Close
(i.e. do not add any tables)
Then, on the ribbon, Query Type, Union
A window will open for you to write a SQL query.
*****
I will give you a few definitions before I give you the SQL, which will solve the above two problems.
SELECT = the fields you wish to select for the query. Notice the [] brackets. These need to be used and are separated by a comma.
FROM = the table where the fields are located.
WHERE = is like setting the criteria in a query.
In
the resulting query, the field names will be those contained in the
first SELECT statement. The second Union Select, the field names will be
ignored.
The field names in the second SELECT statement can be different, but must have the same data type.
Type the following in the blank window provided:
SELECT ID FROM OneBeforeAfter1
UNION SELECT PlusOne FROM OneBeforeAfter1
UNION SELECT MinusOne FROM OneBeforeAfter1
UNION SELECT MinusTwo FROM OneBeforeAfter1
ORDER BY ID;
Once again, changing the ID field to your ordinal field.
Then, use the results of this query to build another query that will display other fields, if needed.
Looking for another example of a Union Query then try:
