I took my own advice and looked this up on Wrox Books 24x7. I found a good entry in Expert One-on-One: Microsoft Access Application Development by Helen Feddema:
"To start making a union query, make a select query based on one of the tables, and create any concatenated fields you need in Design view (you wonât have this opportunity once you convert the query to a union query). In this case, I made a select query based on tblCustomers, with CustomerID, address fields, and a calculated ShipName field...
Next, switch to SQL view. The initial SQL statement of the query is:
SELECT tblCustomers.CustomerID, [ContactFirstName] & â â & [ContactLastName]
AS ShipName, tblCustomers.BillingAddress, tblCustomers.City, tblCustomers.StateOrProvince,
tblCustomers.PostalCode FROM tblCustomers;
(She then removes the table names in front of each field name)
The syntax for a concatenated or aliased field is in the opposite order from that used in Design view: the expression comes first, then the AS keyword, then the field name ([ContactFirstName] & â â & [ContactLastName] AS ShipName).
Next, create another select query based on the second table (in this case, tblShippingAddresses), including all the fields except ShipDepartment, and switch to SQL view. After trimming out the table names, the second SQL statement is as follows:
SELECT ShipAddressID, CustomerID, AddressIdentifier, ShipName, ShipAddress,
ShipCity, ShipStateOrProvince, ShipPostalCode FROM tblShippingAddresses;
To convert the query into a union query, in the first queryâs SQL statement, remove the final semicolon, and type in UNION followed by a space, on a new line under the SQL statement. Switch to the second query, copy its SQL statement, return to the first query, and paste the text in after UNION. The resulting SQL statement is listed below:
SELECT CustomerID, [ContactFirstName] & â â & [ContactLastName] AS ShipName,
BillingAddress, City, StateOrProvince, PostalCode FROM tblCustomers
UNION SELECT ShipAddressID, CustomerID, AddressIdentifier, ShipName, ShipAddress, ShipCity,
ShipStateOrProvince, ShipPostalCode FROM tblShippingAddresses;
There is still some work to do. The component queries in a union query must have the same number of columns (fields), and the matching fields must be of matching data types (however, they donât have to have the same names). There is no ShipAddressID field in tblCustomers, so I created a dummy field with a value of 0 for this purpose. Similarly, I made a dummy field for AddressIdentifier, with a value of âBilling Address.â I also aliased BillingAddress as Address, to make a more generic field name in the union query (the field names of the union query are the ones in the first component query). The final SQL statement is as follows:
SELECT 0 AS ShipAddressID, CustomerID, âBilling Addressâ AS AddressIdentifier,
[ContactFirstName] & â â & [ContactLastName] AS ShipName, BillingAddress AS Address, City,
StateOrProvince, PostalCode FROM tblCustomers
UNION SELECT ShipAddressID, CustomerID, AddressIdentifier, ShipName, ShipAddress, ShipCity,
ShipStateOrProvince, ShipPostalCode FROM tblShippingAddresses;
Now the query can be saved with the tag quni"
(She uses quni to designate a Union query, but you can use your own naming convention.)
HTH
mmcdonal
Look it up at:
http://wrox.books24x7.com