Union with text item

[Jim]

Hi,

I have a combobox based on query that extracts a list of
all the countries we have imported from. I want to add an
option at the top of this (in the combobox) that says "All
Countries", but I don't know how to do the SQL... this is
what I have tried but it doesn't work:

SELECT DISTINCT [Imports].[Country] FROM [Imports] UNION
ALL SELECT "All Countries" FROM "All Countries";

Anyone know how I should be doing it???

Thanks loads,

Jim

 

[John Spencer (MVP)]

I would just use the following and let the UNION (vs UNION ALL) eliminate the
duplicates. Note that you MUST have a tablename in the select statement, but it
can be any table. If you have a one record table of some type, you can use it
instead of the same table.

SELECT [Imports].[Country] FROM [Imports]
UNION
SELECT "All Countries" FROM [Imports];

or

SELECT DISTINCT [Imports].[Country] FROM [Imports]
UNION ALL
SELECT DISTINCT "All Countries" FROM [Imports];

 

[Jim]

Thanks John and Dale,

I really appreciate both your time and your efforts.

I have done something similar to John's first statement:

SELECT "All Countries" FROM [Imports]
UNION
SELECT [Imports].[Country] FROM [Imports];

by putting the "All Countries" bit before the union, and
the countries after, it seems to always put "All
Countries" first.
Even though I know I could not then give an order by
statement specifically for the Country field, for some
reason it seems to automatically sort them alphabetically
after "All Countries".

Do you know why? Will it always work? I hope so!!

Thanks again,

Jim