Union Query

[naomi]

I have a macro which does three queries at once to come up with my results. Problem is.... I am dealing with about a million records, so in order for the reports to be generated by my department to go faster, these queries make tables. But as you know, every time a query is generated... it deletes the old table and builds a new one. That messes up my next query, because I need the results from the last query, and my design view shows it as a table that is not there. It's there, but there are no fields. Would a union query help me at all? If so, how?

 

[chenn]

A union query may run a little faster although I don't think it would be to much faster. Although, if you didn't need to insert the records into a table that could also speed things up.

I use union queries all the time. They are great for problems like this when you need all the data together from several tables. The main thing to remember about union queries is that the fields must be the same in each part of the query.

Select Account, ID from tblAccts
Union Select Account, ID from tblNewAccts

A union query is simply two select queries put together with the word 'union'. An easy way to create a union query is to create two (or more) queries and copy their sql from the query window and paste into another query window attaching each with the word union.

Union queries are select queries. If you need to use them as an update or append query, build another query based on the union query to run the update.