Re: Value in union query

Discussion in 'Microsoft Access Queries' started by John W. Vinson, Apr 12, 2010.

  1. On Mon, 12 Apr 2010 22:26:53 GMT, "szag via AccessMonster.com" <u2885@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]
     
    John W. Vinson, Apr 12, 2010
    #1
    1. Advertisements

  2. On Mon, 12 Apr 2010 23:09:04 GMT, "szag via AccessMonster.com" <u2885@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]
     
    John W. Vinson, Apr 13, 2010
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    To union query or not to union query

    Guest, Feb 3, 2005, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    345
    Guest
    Feb 4, 2005
  2. Olga

    UNION or UNION ALL query question

    Olga, Apr 6, 2006, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    412
    Guest
    Apr 6, 2006
  3. Guest

    UNION vs UNION ALL

    Guest, Jul 31, 2006, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    428
    Michel Walsh
    Jul 31, 2006
  4. Phil Smith

    A union B works, A union B union C fails

    Phil Smith, Nov 28, 2006, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    523
    Phil Smith
    Nov 29, 2006
  5. kev100 via AccessMonster.com

    Can 2 Union Queries Be used in Another UNION ??

    kev100 via AccessMonster.com, Jul 30, 2007, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    238
    Guest
    Jul 30, 2007
Loading...

Share This Page