- By Andrew Couch
- Selecting all columns from a table
- Selecting individual columns from one or more tables
- Joining tables to see unmatched or missing records
- Filtering by single and multiple combinations of choices
- Adding calculations with the expression builder
- Returning the top matched records
- Eliminating duplicate values
- Creating a summary calculation
- Prompting to filter data with parameters
- Creating a crosstab query with the Query Wizard
- Simplifying a problem with a query by using other queries
- Adding two sets of query results together
- Resolving ambiguous outer joins
- Creating an additional query to resolve a problem with mixed joins
Adding two sets of query results together
When you have two or more sets of data that have the same number of columns, same column order, and similar column data types, and you want to combine them into a single list (although this cannot be graphically displayed), you can do so by creating a special type of query called a UNION query. This query uses a special keyword UNION (meaning bring together) or UNION ALL (meaning bring together and show duplicates). You can combine multiple blocks of SQL with this keyword.
In our example, we have two tables, called Customers and OldCustomers, and we want to show the data in both tables in a single list.
Add two result sets together
In design view, add your first table to the query grid and display the desired fields.
Switch the query to SQL View.
Remove the semicolon (;) from the end of the SQL query, highlight the SQL query, and copy the SQL query to the clipboard.
On a new line, type the keyword UNION.
On a new line, paste in the copied SQL query, and change any references in the copied SQL query to refer to the second table and column names. The order of fields, number of fields, and data types should be identical in both sections of the SQL query. Field names can be different.