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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. gip500

    union query and new filed with fixed value

    gip500, Aug 14, 2004, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    892
    '69 Camaro
    Aug 14, 2004
  2. Guest

    To union query or not to union query

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

    UNION or UNION ALL query question

    Olga, Apr 6, 2006, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    1,008
    Guest
    Apr 6, 2006
  4. Guest

    UNION vs UNION ALL

    Guest, Jul 31, 2006, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    746
    Michel Walsh
    Jul 31, 2006
  5. 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:
    861
    Phil Smith
    Nov 29, 2006
  6. 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:
    499
    Guest
    Jul 30, 2007
  7. Union or Union All

    , Feb 5, 2008, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    375
    KARL DEWEY
    Feb 5, 2008
  8. Union or Union All

    , Feb 5, 2008, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    477
    Marshall Barton
    Feb 5, 2008
Loading...