Graphical Union-Query Builder?

[David McCulloch]

I have two moderately complex queries (each one has at least 7-tables and
several more joins) that must be combined via a Union query. The results
will be used in subsequent queries. Writing and maintaining SQL for this
Union query is a last resort. How can I keep my application as intuitive
and as low-maintenance as possible?

1. Does Microsoft or anyone else have a graphical user interface for
building Union queries yet?

2. Does any tool exist that could read user-specified Select queries in
Access and create the corresponding Union query? I've been considering
writing such a tool out of necessity.

Dave

 

[Rob Parker]

Hi David,

Microsoft (ie. Access) doesn't have one, and I'm not aware of any third
party ones.

That said, creating a Union query isn't that difficult. Simply build each
part as a separate query, ensuring that they all have the same number of
fields (they don't have to have the same names - the Union query will use
the fields names from the first sub-query), in the same order. You can
insert null or constant fields in any of the individual select queries if
necessary to get the correct number of fields, in the correct order. Then
open each one in SQL view, and cut/paste into the SQL view of your new Union
query, replacing the closing semicolon of each one with "UNION " (or "UNION
ALL " if you don't want to exclude duplicate records). It's pretty quick in
practice.

HTH,

Rob

 

[Jamie Collins]

Microsoft (ie. Access) doesn't have one, and I'm not aware of any third
party ones.

Ditto a subquery builder (and proabably the reason why CHECK
constraints remain unexposed in the Access interface).

cut/paste [each query] into the SQL view of your new Union
query, replacing the closing semicolon of each one with "UNION " (or "UNION
ALL " if you don't want to exclude duplicate records).

Another reason to use UNION ALL is when you *know* you have no
duplicates and you don't want to take a performance hit while the
engine discovers this for itself. Also consider that UNION will affect
columns of type MEMO in that only the first 255 characters are used to
test for duplication.

Jamie.

--

 

[David McCulloch]

Rob,

Thanks for the information. You've saved me a good chunk of time.

I didn't know that fields of subsequent queries in a Union could have
different names. If they happen to have the same names, do I still need to
qualify the field names (t1.field1, t2.field1, etc.) for WHERE and/or ON
conditions?


+ + + + + + + + + + + +
For Microsoft's benefit
+ + + + + + + + + + + +

If Microsoft is listening, please provide a graphical method for building
and maintaining Union queries. Here are a few drawbacks of manually
building Union queries, even from the SQL associated with graphically
constructed Select queries:

1. It requires anyone who builds or maintains an application to know
something about SQL. Often, they will be functional users, not IT
professionals.

2. It requires anyone who maintains an application to know which Select
queries were used to build the Union query. The original programmer might
be able to make it more intuitive with good query naming standards, but
nothing would replace an even moderately designed graphical interface that
would display the Union's individual query names.

3. It requires much more maintenance time -- and time is money -- than if
Microsoft provided this relatively simply graphical interface for building
and maintaining Union queries (sorry for the venting!).

Dave

 

[Rob Parker]

Hi Dave,

If you are limiting the input in each of the sub-queries in your Union
query, then the easier way is to build the Union from the subqueries (which,
if you've followed my earlier advice, will all have the same corresponding
fields), rather than from the subqueries SQL.

So, rather than:
SELECT ...
FROM tblOne
WHERE tblOne.FieldX = ...
UNION ALL
SELECT ...
....

Use the filtered queries as the input to your Union query:
SELECT *
FROM qryFilteredQueryOn TableOne
UNION ALL
SELECT ...

If you want to limit the results from your Union query, then create a new
query based on the union query, and apply the WHERE conditions in that
query.

Again, HTH,

Rob

PS. I noticed your "For Microsoft's Benefit" section. Although this
newsgroup is hosted by MS, they do not (officially) monitor it; and the MVPs
who reply to most questions are not Microsoft employees.

In particular, I think your first comment is far from well-founded.
Building/maintaining an Access application is not something which can be
done by a "functional IT user"; it requires a lot of particular skills and
knowledge. Most "functional IT users" will not know anything about SQL.
And those things, together, are probably why there is no graphical method
for building a Union query: for occasional users (including those who are
reasonably "IT-savvy"), it's beyond their comprehension; for Access
professionals, it's so simple that a graphical interface is not needed.

 

[David McCulloch]

Rob,

Thanks again for the information. I did not know that I could reference
queries in a Union query. That indeed helps a lot! You have allowed me to
continue my work and eliminated much frustration in future projects.

Dave