Combine Multiple Queries

[IllinoisREI]

My list of queries is growing out of control. I frequently find myself creating multiple select queries to be used to create other select queries because of the need for unique joins at each stage.

I've combined queries with union queries using straight sql but is there a similar approach where select queries that build on other select queries can be combined in one? Perhaps using code? My scroll wheel is getting worn out!

Thanks

 

[sxschech]

If you actually want to have everything in one query, you could do it in SQL something like below:

This example shows having unions and then a select query on top of it. You can also do additional joins to other queries.

SELECT a.ID, a.NAME, a.CITY_CODE, b.CITY, b.ORDER_NUMBER
FROM ORDER_TABLE a
LEFT OUTER JOIN 
(
SELECT ID, NAME, CITY_CODE
FROM TABLE1
WHERE COUNTRY_CODE IN ('US','UK','FR')
UNION SELECT ID, NAME, CITY_CODE
FROM TABLE2
WHERE CITY_CODE ='45'
) b
ON a.CITY_CODE = b.CITY_CODE

Also, you could use the technique to be able to work with union queries in the graphical query grid view. One weird thing that sometimes happens though when you go back to edit the query, Access adds square brackets [] around the query and a funny alias name with # symbols. If access gives an error message when you try to run the query, after a modification, you would need to go into sql view to change the square brackets back to parenthesis () and delete the [red]. AS [%$##@_Alias][/red];

To use union queries in query design view, first create the union queries in sql view, then switch to design view and continue with adding the other fields/criteria you want.

SELECT ID
FROM (
SELECT ID, NAME, CITY_CODE
FROM TABLE1
WHERE COUNTRY_CODE IN ('US','UK','FR')
UNION SELECT ID, NAME, CITY_CODE
FROM TABLE2
WHERE CITY_CODE ='45'
) b