SQL select help

  • Thread starter mattc66 via AccessMonster.com
  • Start date
M

mattc66 via AccessMonster.com

SELECT *
FROM [TerritoryCoverage_finnleo]

UNION SELECT *
FROM [TerritoryCoverage_helo];

I want to also add a column that will be BRAND: "BrandName". How would I add
this doing a SQL statement.

Thanks
 
M

Mark Burns

Matt,

You will need to ditch the * syntax and specify the column names.
In doing so, you can add " Null as BrandName " and/or " 'Whatever' as
BrandName ", or possibly even a correlated subquery like:

SELECT Field1, Field2, (SELECT First(myBrand) FROM WhatEverTable WHERE
WhateverTable.myField = TCF.Field2 GROUP BY myBrand) as BrandName, Field3
FROM [TerritoryCoverage_finnleo] As TCF
UNION...

(Don't take the above as gospel on the SELECT syntax - especially for the
subquery, but the illustration of how to use a correlated subquery was my
point.)

However, using this correlated subquery syntax may make your query subject
to destructive modification by the Access Query Editor - if you do this, only
use the SQL editor mode, and not the graphical query designer mode when
examining/modifying this query. (Oops, silly me, you're doing a UNION query
anyway...you can disregard that last part as the SQL editor mode is the only
game in town for you already.)
 
K

KARL DEWEY

SELECT *, "BrandName" AS BRAND
FROM [TerritoryCoverage_finnleo]

UNION SELECT *, "BrandName" AS BRAND
FROM [TerritoryCoverage_helo];
 
Top