Union Query--Joining Multiple Queries Together

Example--you want to show all records from ALL tables modified on the current date, in the same list, using 1 query

Also Shows How to Add a Field to a Query (Which Doesn't Exist in the Root Tables) and Assign a Value to It

Thursday, November 15, 2001 10:55:12

One common thing I've always wanted to do in Access but never knew how to do until today was to join several queries together into 1, and do so without the need for a report combining other sub-reports together, or forms doing this, or what not. I wanted ONE query which united other queries together into 1.

To do this, you need a UNION query. This requires knowledge in SQL statements. What I did was create a query for each scenario, and then "View...SQL" and copied/pasted the SQL statements into a text editor. To experiment with this, the 1st query I designed was to show every record from the "mold" table modified on Novemer 14th, 2001. Then, another query was designed which showed every record from the "samples" table modified on Noveber 9th, 2001. Lastly, another query was designed to show all entries from the "purchase requisition" table modified on "November 13th, 2001."

After designing each query, I executed the "View....SQL" statement, copied/pasted the code into a text-editor, and then removed (except for the last query) the ; character which, in SQL language, means the statement is over. I then inserted "UNION ALL" between each batch of SQL code. The resulting SQL statements follow:

SELECT mold_table.autoid, mold_table.mold_description AS description, mold_table.mold_number AS mold_sample_number,
mold_table.date_modified, "Mold" AS data_type
FROM mold_table
WHERE (((mold_table.date_modified)=#11/14/01#))

UNION ALL

SELECT samples_table.sample_id, samples_table.type_of_request AS description, samples_table.request_number AS mold_sample_number,
samples_table.date_modified, "Sample" AS data_type
FROM samples_table
WHERE (((samples_table.date_modified)=#11/9/01#))

UNION ALL

SELECT purchase_req_table.autoid, purchase_req_table.po_entered_by AS description, purchase_req_table.po_number,
purchase_req_table.date_modified, "Purchase Req" AS data_type
FROM purchase_req_table
WHERE (((purchase_req_table.date_modified)=#11/13/01#));

The resulting query looks like the following:

autoid	description	mold_sample_number	date_modified	data_type
177	Barrel	1264	11/14/01	Mold
222	Lens	2033	11/14/01	Mold
164	New Molded Samples	182	11/9/01	Sample
15			11/13/01	Purchase Req
52			11/13/01	Purchase Req
55			11/13/01	Purchase Req

This resulting code is then pasted into another query (or used within Visual Basic statements). The other queries which were used to generate this statements can then be deleted; they are no longer needed for the UNION query to work.

The "description" field represents the "mold_description" field from the MOLDS table, the "type_of_testing_needed" field from the SAMPLES table, and the "po_entered_by" field from the PURCHASE REQ table. (In each instance, the original query's DESIGN view, the field was renamed "description.") The "data_type" is an additional field which each query adds. With the 1st query, the value assigned to this added field is called "Mold." With the other queries, the value assigned to this field is called "Sample" or "Purchase Req." This way, the user knows whether it's a mold, sample, or purchase requisition they are looking at.

Extra:Adding a New Field to a Query Which Doesn't Exist in the Tables the Query Derives Its Data From

The syntax in the original query's design to add a new field and assign a value to it looks like this (within the column of the query in "design" view, the "Field" row):

data_type: "Purchase Req"