Re: Graphical Union-Query Builder?
- From: "Rob Parker" <NOSPAMrobpparker@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 2 Oct 2007 23:16:45 +1000
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" <|@|.|> wrote in message
news:47023dd3$0$24340$4c368faf@xxxxxxxxxxxxxxxxx
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" <NOSPAMrobpparker@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:ujXld9JBIHA.4568@xxxxxxxxxxxxxxxxxxxxxxx
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
"David McCulloch" <|@|.|> wrote in message
news:4701aad1$0$20566$4c368faf@xxxxxxxxxxxxxxxxx
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
.
- References:
- Re: Graphical Union-Query Builder?
- From: Rob Parker
- Re: Graphical Union-Query Builder?
- Prev by Date: Re: query multiple tables with identical column names
- Next by Date: Re: Filtering Queries
- Previous by thread: Re: Graphical Union-Query Builder?
- Next by thread: how is "FIRST" supposed to work
- Index(es):
Relevant Pages
|
