[vox-tech] SQL help - unions

[Michael J Wenk]

On Wed, Jul 21, 2004 at 05:00:36AM -0700, Peter Jay Salzman wrote:
> On Wed 21 Jul 04,  1:36 AM, David Hummel <dhml at comcast.net> said:
> > On Wed, Jul 21, 2004 at 12:00:39AM -0700, Jeff Newmiller wrote:
> > > 
> > > A headcount is not the same as a roster... neither Peter's nor
> > > Michael's version was counting anything.
> > 
> > I assumed what Peter was after was a list of unique SSN's for "race = 0"
> > students in Fall_2003 and Spring_2004.
>  
> Unfortunately, I'm being forced to use MS Access at work.  In Access, a
> roster always gives you a headcount (but a headcount doesn't necessarily
> give you a roster).  So a roster is good too.

What sort of DB backend are you using?  A mdb file? or some sort of ODBC 
source ?  Be careful that the backend supports what you are trying to do. 

> Actually, Access is kind of neat, but it has a major drawback.  You can
> build fairly complex queries with a graphical interface.  You can also
> build them using SQL.  But the GUI is very convenient.  Until you hit
> something the GUI can't do, like this.

One of my pet peeves about access is it tends to create complexity when
none is needed, and trying to read its queries is annoying. :)  But I 
agree that access is powerful, and decent in its own right.  Then again
I try to stick with just straight SQL, but I do try to avoid GUI's if I 
can. 
 
> I've been told the GUI query builder can't accomplish this task because
> the database is designed poorly, as Jeff pointed out.  But these are
> actually text files written by a mainframe that are sent to the state to
> report various things about our college.  They weren't meant to be part
> of a database.  That was my idea.  :)

Well, you can take your data and create a schema to match it.  The idea
is to normalize and denormalize your database until you get a schema 
that suits your needs(performance, complexity, and space)  Even if your
stuck with a database schema that doesn't work, you can always make some
changes.  For instance, you can use the old table to create a temp table
in the scratch area and use that table for most of your queries.  

 
> The downside of using Access (other than the obvious) is that you tend
> to rely on the GUI interface when you should be concentrating on your
> basic SQL skills to prepare yourself for the inevitable time when you
> HAVE to use SQL because the GUI can't do it (like now - there's no GUI
> for unions).

That is one reason why I stuck to just straight SQL rather than any GUI 
when I learned RDBMS programming.  One other thing that sometimes kills 
me is switching engines.  While all these guys speak SQL, its the variants
that kill you.