Value in union query

[szag via AccessMonster.com]

I have used union queries before but since I am not much of a SQL person I am
having a little trouble with the following:

I have a table called Jobs and I want to return all the values in the job
name filed. However I also want to add one more value - a hard coded value
called "Manager Override" to the values returned from the job name field. How
can you combine values from a table/field with another value (that is hard
coded into the union query)?

(I am using the results for a combobox).

Thanks for any help.

 

[John W. Vinson]

I have used union queries before but since I am not much of a SQL person I am
having a little trouble with the following:

I have a table called Jobs and I want to return all the values in the job
name filed. However I also want to add one more value - a hard coded value
called "Manager Override" to the values returned from the job name field. How
can you combine values from a table/field with another value (that is hard
coded into the union query)?

(I am using the results for a combobox).

Thanks for any help.

You can include a text literal in one of the SELECT clauses of the UNION, e.g.

SELECT 0 AS JobID, "<Manager Override>" AS JobTitle
FROM Jobs
UNION ALL
SELECT JobID, JobTitle FROM Jobs
ORDER BY JobTitle;

The < in the string will sort before any letter so the override will appear
first in the combo box.

 

[szag via AccessMonster.com]

Perfect! for future reference - is the 0 in Select 0 used when not coming
from a table or query.

I have used union queries before but since I am not much of a SQL person I am
having a little trouble with the following:

[quoted text clipped - 8 lines]

Thanks for any help.

You can include a text literal in one of the SELECT clauses of the UNION, e.g.

SELECT 0 AS JobID, "<Manager Override>" AS JobTitle
FROM Jobs
UNION ALL
SELECT JobID, JobTitle FROM Jobs
ORDER BY JobTitle;

The < in the string will sort before any letter so the override will appear
first in the combo box.

 

[John W. Vinson]

Perfect! for future reference - is the 0 in Select 0 used when not coming
from a table or query.

Sorry... should have explained!

The various SELECT clauses in a UNION query must match in terms of the number
and corresponding datatypes of the fields. I was guessing (perhaps
incorrectly) that you were selecting a numeric ID and a title from a table;
the 0 and the literal text string were to match those datatypes. You might
need fewer or more literals, just to make the two SELECT clauses work
together.