Consolidate multiple Query Results into a single datasheet

=========
REF:

http://office.microsoft.com/en-us/access-help/combine-the-results-of-several-select-queries-by-using-a-union-query-HA010206109.aspx#BM2a

http://www.databasedev.co.uk/union_query.html

=========
NOTES / WARNINGS
———
Union queries drop out duplicate records by default!

If you want all records, including duplicates, you need to include the term ALL after the term UNION

Example:
<SQL from one Query>
UNION ALL
<SQL from another query>
UNION ALL
<SQL from another query>

=========
HOW TO
———
Create the select queries in Design view first, and then combine them

In this method, you create each select query by using Design view, and then you combine the select queries by using SQL view.

Create each select query in Design view

  1. On the Create tab, in the Other group, click Query Design.
  1. In the Show Table dialog box, double-click the table that has the fields that you want to include.
    • The table is added to the query design window.
    •  Note   Although you can include multiple tables or queries in a select query, this procedure assumes that each of your select queries includes data from only one table.
  2. Close the Show Table dialog box.
  3. In the query design window, double-click each of the fields that you want to include.
    • As you select fields, make sure that you add the same number of fields, in the same order, that you add to the other select queries. Pay careful attention to the data types of the fields, and make sure that they have compatible data types with fields in the same position in the other queries that you are combining.
    • For example, if your first select query has five fields, the first of which contains date/time data, make sure that each of the other select queries that you are combining also has five fields, the first of which contains date/time data, and so on.
  4. Optionally, add criteria to your fields by typing the appropriate expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) in the Criteria row of the field grid.
  5. After you have finished adding fields and field criteria, you should run the select query and review its output.
    • On the Design tab, in the Results group, click Run.
  1. Switch the query to Design view.
  2. Save the select query, and leave it open.
  3. Repeat this procedure for each of the select queries that you want to combine.

Combine the select queries in SQL view

  1. On the Create tab, in the Other group, click Query Design.

A new query opens in Design view.

  1. Close the Show Table dialog box.
  2. On the Design tab, in the Query group, click Union.

The query design window is hidden, and the SQL view object tab is displayed. At this point, the SQL view object tab is empty.

  1. Click the tab for the first select query that you want to combine in the union query.
  2. On the Home tab, in the Views group, click View, and then click SQL View.

The SQL statement for the select query is displayed.

  1. Copy the SQL statement for the select query.
  2. Click the tab for the union query that you started to create at step 1 of this procedure.
  3. Paste the SQL statement for the select query into the SQL view object tab of the union query.
  4. Delete the semicolon (;) that is at the end of the select query SQL statement.
  5. Press ENTER to move the cursor down one line, and then type UNION on the new line.

Optionally, type a space, followed by the ALL keyword, and then press ENTER again.

  1. Click the tab for the next select query that you want to combine in the union query.
  2. Repeat steps 5 through 11 of this procedure until you have copied and pasted all of the SQL statements for the select queries into the SQL view window of the union query. Do not delete the semicolon or type anything following the SQL statement for the last select query.
  3. On the Design tab, in the Results group, click Run.

The results of your union query appear in Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.).

=========
APPLIES TO

Microsoft Access
Microsoft SQL


http://www.anysitesupport.com/consolidate-multiple-query-results-into-a-single-datasheet/

By | 2017-12-01T23:47:52+00:00 March 30th, 2011|Access, Computers, Microsoft|0 Comments