Title of Field in Union Query

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I got stuck on something that should be pretty simple, but a solution is not
presenting itself, so I though I'd post here.

SELECT [RVP], [Director], [SalesRep], [Advertiser], [C1 Q109 Pipe], "C1 Q109
Pipe" as Product FROM [Revenue-Book-Pipe]
UNION ALL Select [RVP], [Director], [SalesRep], [Advertiser], [C1 Q109
Rev-Book], "C1 Q109 Rev-Book" as Product FROM [Revenue-Book-Pipe];

I keep getting a field name of "C1 Q109 Pipe". I really wanted a generic
name for this filed, like "Amount". How do I do this?

TIA,
Ryan---
 
My guess is that you don't need the "as Product" in both parts of the Union.
Try it just at the top or bottom, but not both.
 
Thanks! That's what I thought, but the title of the Field actually comes
before that. Any other suggestions?

Thanks,
Ryan---

--
RyGuy


S.Clark said:
My guess is that you don't need the "as Product" in both parts of the Union.
Try it just at the top or bottom, but not both.

ryguy7272 said:
I got stuck on something that should be pretty simple, but a solution is not
presenting itself, so I though I'd post here.

SELECT [RVP], [Director], [SalesRep], [Advertiser], [C1 Q109 Pipe], "C1 Q109
Pipe" as Product FROM [Revenue-Book-Pipe]
UNION ALL Select [RVP], [Director], [SalesRep], [Advertiser], [C1 Q109
Rev-Book], "C1 Q109 Rev-Book" as Product FROM [Revenue-Book-Pipe];

I keep getting a field name of "C1 Q109 Pipe". I really wanted a generic
name for this filed, like "Amount". How do I do this?

TIA,
Ryan---
Click to expand...
Click to expand...
 
Field names in a UNION query come from the first query. If you want a more
generic name, then alias the field in the first query.

SELECT [RVP], [Director], [SalesRep], [Advertiser]
, [C1 Q109 Pipe] as Amount, "C1 Q109 Pipe" as Product
FROM [Revenue-Book-Pipe]
UNION ALL
Select [RVP], [Director], [SalesRep], [Advertiser],
[C1 Q109 Rev-Book], "C1 Q109 Rev-Book" as Product
FROM [Revenue-Book-Pipe];

Note that you do not even need to apply an alias to [C1 Q109 Rev-Book] in the
second query in the Union query. You could even apply a different alias and
it would be ignored. The field names come from the first query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
That did it! I learned something new today. I've done something similar;
but I was just adding one column, I didn't know how to do it for two columns.
Thanks for the help John!!

Ryan---


--
RyGuy


John Spencer said:
Field names in a UNION query come from the first query. If you want a more
generic name, then alias the field in the first query.

SELECT [RVP], [Director], [SalesRep], [Advertiser]
, [C1 Q109 Pipe] as Amount, "C1 Q109 Pipe" as Product
FROM [Revenue-Book-Pipe]
UNION ALL
Select [RVP], [Director], [SalesRep], [Advertiser],
[C1 Q109 Rev-Book], "C1 Q109 Rev-Book" as Product
FROM [Revenue-Book-Pipe];

Note that you do not even need to apply an alias to [C1 Q109 Rev-Book] in the
second query in the Union query. You could even apply a different alias and
it would be ignored. The field names come from the first query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I got stuck on something that should be pretty simple, but a solution is not
presenting itself, so I though I'd post here.

SELECT [RVP], [Director], [SalesRep], [Advertiser], [C1 Q109 Pipe], "C1 Q109
Pipe" as Product FROM [Revenue-Book-Pipe]
UNION ALL Select [RVP], [Director], [SalesRep], [Advertiser], [C1 Q109
Rev-Book], "C1 Q109 Rev-Book" as Product FROM [Revenue-Book-Pipe];

I keep getting a field name of "C1 Q109 Pipe". I really wanted a generic
name for this filed, like "Amount". How do I do this?

TIA,
Ryan---
Click to expand...
Click to expand...
 
Back
Top