Prev: please explain [], . and !
Next: Rank and row number in Access?
From: szag via AccessMonster.com on 12 Apr 2010 18:26
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1

From: John W. Vinson on 12 Apr 2010 18:53
On Mon, 12 Apr 2010 22:26:53 GMT, "szag via AccessMonster.com" <u2885(a)uwe>
wrote:

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

John W. Vinson [MVP]
From: szag via AccessMonster.com on 12 Apr 2010 19:09
Perfect! for future reference - is the 0 in Select 0 used when not coming
from a table or query.

John W. Vinson wrote:
>>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.

--
Message posted via http://www.accessmonster.com

From: John W. Vinson on 12 Apr 2010 19:28
On Mon, 12 Apr 2010 23:09:04 GMT, "szag via AccessMonster.com" <u2885(a)uwe>
wrote:

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

John W. Vinson [MVP]
 | 
Pages: 1
Prev: please explain [], . and !
Next: Rank and row number in Access?