SQL query riddlle

[Phlip]

Newsgroupies:

Access makes binding controls to SQL Select statements so easy, that one
might as well write a complex Select statement rather than write VBA code to
back up a control.

So given Table X with fields Fruit, Fish, and Fur, I want a list box
containing this:

Apple
Ferret
Mahi-Mahi
Orange
Perch
Puppies

The one list box mixed together data from three columns in one table. Is
such a SQL statement even possible?

 

[Van T. Dinh]

Check Access Help on Union Queries.

HTH
Van T. Dinh
MVP (Access)

 

[John Vinson]

Newsgroupies:

Access makes binding controls to SQL Select statements so easy, that one
might as well write a complex Select statement rather than write VBA code to
back up a control.

So given Table X with fields Fruit, Fish, and Fur, I want a list box
containing this:

Apple
Ferret
Mahi-Mahi
Orange
Perch
Puppies

The one list box mixed together data from three columns in one table. Is
such a SQL statement even possible?

A UNION query is the ticket here:

SELECT [Fruit] FROM tablename
UNION ALL
SELECT [Fish] FROM tablename
UNION ALL
SELECT [Fur] FROM tablename
ORDER BY 1;

John W. Vinson[MVP]

 

[Phlip]

Check Access Help on Union Queries.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/ima0137.asp

That did it - thanks!

Select Fruit from X Union Select Fish from X

But...

Still trying to play "stump SQL", how can I get the query into alpha order?

(I'm aware I can put the combo box into alpha order. This is just an
exercise.)

 

[Van T. Dinh]

Select Fruit from X Union Select Fish from X
ORDER BY [Fruit]

The ORDER BY clause will order the result (rows) of the Union.

HTH
Van T. Dinh
MVP (Access)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/ima0137.asp

That did it - thanks!

Select Fruit from X Union Select Fish from X

But...

Still trying to play "stump SQL", how can I get the query into alpha order?

(I'm aware I can put the combo box into alpha order. This is just an
exercise.)

 

[Phlip]

Select Fruit from X Union Select Fish from X
ORDER BY [Fruit]

The ORDER BY clause will order the result (rows) of the Union.

Great! Now one must wonder why it works. In terms of designing the SQL
language, if the Order operator precedes Union then it should not work. The
Order by should work only on Fish, and should sub-sort only its branch of
the Union.

That's not a question. Thanks for giving me the nudge to try Order
counterintuitively...

 

[Van T. Dinh]

That's my assessment also.

 

[Phlip]

Use a wizard to build a ComboBox with RowSourceType SQL Statement. Replace

the statement with a hand-written one [containing Union, which may or may
not be relevant].

Now it no worky. The ComboBox seems to have enough rows for the data I
expect, but no data appears.

What's up with that? Do I need to put [] around everything?

Here's what it was:

Per my 400 years of experience with VB Classic, you paint a combo box by
dragging a big rectangle. The height represents the number of elements when
it drops down. So, in Access, I painted a tall ComboBox and ran it.

Then it looked like it had nothing in it, because you paint an Access
ComboBox by sizing its entry field, and I was just getting a big tall one.

I have since figured out what the little [v] button is for.

Yet more evidence some folks sure shouldn't be set loose anywhere near a
computer, huh?

 

[Dirk Goldgar]

Here's what it was:

Per my 400 years of experience with VB Classic,

What is that in dog years?

you paint a combo box
by dragging a big rectangle. The height represents the number of
elements when it drops down. So, in Access, I painted a tall ComboBox
and ran it.

Then it looked like it had nothing in it, because you paint an Access
ComboBox by sizing its entry field, and I was just getting a big tall
one.
Ah.

I have since figured out what the little [v] button is for.

Yet more evidence some folks sure shouldn't be set loose anywhere
near a computer, huh?

<lol>

Y'see, if you were a newbie to computers and programming, you'd read the
nice manual -- well, online help file. Okay, so it's not so nice, if
you're trying to find a particular piece of information. But it's still
pretty good if you read it like a book.

If you're coming from VB-land, you'll find that Access data-centric
controls are way better than their VB equivalents. But they're enough
different that you can't take things for granted.

 

[Phlip]

What is that in dog years?

It felt like it.

Y'see, if you were a newbie to computers and programming, you'd read the
nice manual -- well, online help file. Okay, so it's not so nice, if
you're trying to find a particular piece of information. But it's still
pretty good if you read it like a book.

The agony of wading thru endless reams of "now click on the Start menu, now
point to Programs, now click on Microsoft Access" would numb me to the few
details that I actually needed to know. Believe me I have tried.

If you're coming from VB-land, you'll find that Access data-centric
controls are way better than their VB equivalents. But they're enough
different that you can't take things for granted.

Of course! Access ComboBoxes permit multi-line edit fields because they are
data-centric!

(You guys sure have an excuse for everything, huh?

 

[Dirk Goldgar]

The agony of wading thru endless reams of "now click on the Start
menu, now point to Programs, now click on Microsoft Access" would
numb me to the few details that I actually needed to know. Believe me
I have tried.

It's lucky you have these wonderful newsgroups to ask questions in,
then.

Of course! Access ComboBoxes permit multi-line edit fields because
they are data-centric!

I'm not sure what you mean by "multi-line edit fields", so I don't know
what part of that is sarcastic.

(You guys sure have an excuse for everything, huh?

It's what we live for.