PC Review

Thread Tools Rate Thread

Re: Value in union query

John W. Vinson
Posts: n/a
      12th Apr 2010
On Mon, 12 Apr 2010 22:26:53 GMT, "szag via AccessMonster.com" <u2885@uwe>

>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
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]
Reply With Quote
John W. Vinson
Posts: n/a
      13th Apr 2010
On Mon, 12 Apr 2010 23:09:04 GMT, "szag via AccessMonster.com" <u2885@uwe>

>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

John W. Vinson [MVP]
Reply With Quote

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
A union B works, A union B union C fails Phil Smith Microsoft Access Queries 2 29th Nov 2006 06:50 PM
Marshalling a struct (which contains a union and the union contains a struct ... also all the members are byte arrays) for Platform Invoke jsshah Microsoft C# .NET 1 22nd Jul 2006 03:11 AM
UNION or UNION ALL query question Olga Microsoft Access Queries 2 6th Apr 2006 08:49 PM
how could i default a value in a multi union query if the query didn't have value to return?? Tracy Microsoft Access 3 14th Jan 2006 01:12 AM
To union query or not to union query =?Utf-8?B?U2V0aCBTY2h3YXJt?= Microsoft Access Queries 2 4th Feb 2005 11:29 PM




All times are GMT +1. The time now is 10:00 PM.