Union Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question.

I want to create a list of products with NONE at the top of the list.

What I thought I'd do is have one table with the word NONE in it, and
another table with all the products in it. Then I'd make a union query,
bringing them together.

I put a select statement selecting the NONE value from the table that holds
it first, and then a select statement selecting all the product names from
the other table.

I assumed that the union query would display NONE first then all the product
names. What I'd like to do is create a query that alphabetizes the product
names and union that to the table that holds NONE as a value. That way in my
combo box, I'll have NONE as the first value and then all the products listed
alphabetically after that.

What I am getting when I use just the two tables is the result ALPHABETIZED!
I have no idea why they are alphabetized. There is no sorting.

Please guide me before I smash my computer.

Thanks again,

Brad

Here is my SQL:

SELECT tblNone.FieldNone
FROM tblNone

UNION SELECT tblMed.Med
FROM tblMed;

tblMed values are
Record 1 = Tylan
Record 2 = Flavo.

tblNone value is
Record 1 = NONE
 
I forgot to add...

My results look like this

Flavo
NONE
Tylan

I would like

NONE
Flavo
Tylan

Thanks

Brad
 
Brad said:
Thanks for taking the time to read my question.

I want to create a list of products with NONE at the top of the list.

What I thought I'd do is have one table with the word NONE in it, and
another table with all the products in it. Then I'd make a union query,
bringing them together.

I put a select statement selecting the NONE value from the table that holds
it first, and then a select statement selecting all the product names from
the other table.

I assumed that the union query would display NONE first then all the product
names. What I'd like to do is create a query that alphabetizes the product
names and union that to the table that holds NONE as a value. That way in my
combo box, I'll have NONE as the first value and then all the products listed
alphabetically after that.

What I am getting when I use just the two tables is the result ALPHABETIZED!
I have no idea why they are alphabetized. There is no sorting.

Please guide me before I smash my computer.

Thanks again,

Brad

Here is my SQL:

SELECT tblNone.FieldNone
FROM tblNone

UNION SELECT tblMed.Med
FROM tblMed;

tblMed values are
Record 1 = Tylan
Record 2 = Flavo.

tblNone value is
Record 1 = NONE
Click to expand...

Hi Brad,

1. This seems an odd thing to want to do. Putting some text or other in
a combo box to indicate that nothing has been chosen may be customary in
HTML forms, but it's not usually done in Windows applications.

2. "There is no sorting." Exactly. The order of results from an SQL
query is undefined, unless you specify an ORDER BY clause. It is a mistake
to assume that, in the absence of an ORDER BY clause, the results will be in
any particular order. It depends entirely on the way the database engine
chose to execute the query.

3. To get your results ordered as you want, you could do something like
this:

(SELECT tblNone.FieldNone AS Med, 0 AS Sequence FROM tblNone)
UNION
(SELECT Med, 1 AS Sequence FROM tblMed)
ORDER BY Sequence, Med
 
Brad said:
I want to create a list of products with NONE at the top of the list.

What I thought I'd do is have one table with the word NONE in it, and
another table with all the products in it. Then I'd make a union query,
bringing them together.

I put a select statement selecting the NONE value from the table that holds
it first, and then a select statement selecting all the product names from
the other table.

I assumed that the union query would display NONE first then all the product
names. What I'd like to do is create a query that alphabetizes the product
names and union that to the table that holds NONE as a value. That way in my
combo box, I'll have NONE as the first value and then all the products listed
alphabetically after that.

What I am getting when I use just the two tables is the result ALPHABETIZED!
I have no idea why they are alphabetized. There is no sorting.

Here is my SQL:

SELECT tblNone.FieldNone
FROM tblNone

UNION SELECT tblMed.Med
FROM tblMed;

tblMed values are
Record 1 = Tylan
Record 2 = Flavo.

tblNone value is
Record 1 = NONE
Click to expand...


To get the records sorted in any particular way, you must
use an Order By clause. In this case, that implies that you
need another field in the query. Try something like:

SELECT tblNone.FieldNone, 0 As SortField
FROM tblNone
UNION
SELECT tblMed.Med, 1
FROM tblMed
ORDER BY 2,1
 
Hi Brad,

I do something very similar with my database, so I would suggest: Instead of
using NONE use "- NONE" (I use : - Not defined). Adding the "-" in front of
it, assure that using the ORDER BY clause, the "- None" will be at the top.


Take care

Mauricio Silva
 
Back
Top