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...

 

[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.

Here's a new question.

Suppose I go to see the Wizard, if I only had a ComboBox bound to a table.
The Wiz builds me one, and it Selects whatever from whatever. And it works.

Now I go to see the little man behind the curtain, and edit the ComboBox's
props and change the RowSource from that Wizard-generated select statement
to

Select Fruit from X Union Select Fish from X

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?

 

[Al Camp]

Now I go to see the little man behind the curtain, and edit the ComboBox's

props and change the RowSource from that Wizard-generated select statement
to

No expalinee... no fixee! :-D
Al camp

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

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

Here's a new question.

Suppose I go to see the Wizard, if I only had a ComboBox bound to a table.
The Wiz builds me one, and it Selects whatever from whatever. And it
works.

Now I go to see the little man behind the curtain, and edit the ComboBox's
props and change the RowSource from that Wizard-generated select statement
to

Select Fruit from X Union Select Fish from X

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?

 

[Phlip]

No expalinee... no fixee! :-D

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?

 

[Dirk Goldgar]

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

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

Here's a new question.

Suppose I go to see the Wizard, if I only had a ComboBox bound to a
table. The Wiz builds me one, and it Selects whatever from whatever.
And it works.

Now I go to see the little man behind the curtain, and edit the
ComboBox's props and change the RowSource from that Wizard-generated
select statement to

Select Fruit from X Union Select Fish from X

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?

Check the combo box's ColumnCount and ColumnWidths properties, and make
sure they gibe with the number of fields your revised query actually
selects. Quite possibly the wizard included a hidden key column, which
you aren't including.

 

[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.