Need to add a value to a query

[RayMilhon]

I have the following query

SELECT dbo_tbl_HPCODEs.PRODUCTLINE
FROM dbo_tbl_HPCODEs
GROUP BY dbo_tbl_HPCODEs.PRODUCTLINE;

For the purpose of one query I need to add 1 specific value to the results.

My first thought was

SELECT dbo_tbl_HPCODEs.PRODUCTLINE
FROM dbo_tbl_HPCODEs
GROUP BY dbo_tbl_HPCODEs.PRODUCTLINE
Union All
Select "ACO" ;

but I realized that won't work because it's not from a table it's just 1 specific value. The Results from the first query is

COMMERCIAL
COMM-POS
SENIOR
MEDI-CAL


MEDI-CAL EXPANSION
MEDICONNECT
COVERED CA

This is for a combo box and I need to Add "ACO" to that list. Any idea how I can do that. Tried VBA using the AddItem for the combo box but that only works if it's a value list. I need it to be the query.

[ranman256]

you dont use: AddItem
all items should come from a query or table.
if tProductLine is the table then add ACO to the table.

[pbaldy]

Try

SELECT dbo_tbl_HPCODEs.PRODUCTLINE
FROM dbo_tbl_HPCODEs
GROUP BY dbo_tbl_HPCODEs.PRODUCTLINE
UNION
SELECT "ACO"
FROM dbo_tbl_HPCODEs

[Micron]

it's not from a table it's just 1 specific value

So you fake it.

SELECT dbo_tbl_HPCODEs.PRODUCTLINE, 
FROM dbo_tbl_HPCODEs
UNION
SELECT "ACO" AS Whatever FROM 
dbo_tbl_HPCODEs;

It shouldn't matter what you put as the alias (AS part) because it's not going to show anyway, but ACO will be added to the values returned from the table. When you think about it, adding an alias field to a query based on a table is the same thing - it doesn't exist in that table either. Same goes for adding a specific value - it doesn't exist in the table. I left off the Group By since it's not a Totals query and I don't see why it's needed for a combo box. If you're getting multiple results, you need either the DISTINCT or DISTINCT ROW predicate.

Paul, you beat me again. One second you're not there, the next...

[pbaldy]

Fastest fingers in the west!

[RayMilhon]

thank you that worked.

[Micron]

I can only guess as to which you are referring.

[RayMilhon]

Sorry should have been more specific. I added the from so my query now looks like this

SELECT dbo_tbl_HPCODEs.PRODUCTLINE
FROM dbo_tbl_HPCODEs
GROUP BY dbo_tbl_HPCODEs.PRODUCTLINE
UNION ALL select distinct "ACO" as PL
from dbo_tbl_hpcodes;

Works perfectly thanks

[Micron]

Good that it works, but FYI, UNION returns unique values. UNION ALL returns duplicates if there are any. Thus UNION ALL and SELECT DISTINCT are contradictory. Apparently, the latter over-rides the former, or in your case there are no duplicates anyway.

[pbaldy]

You and I both dropped the ALL to get rid of the duplicates that the second SELECT would have returned. I'm guessing what Ray posted still works. The ALL allows duplicates over the whole result set, but the DISTINCT would prevent the second SELECT from returning duplicates. Might even be more efficient, since UNION ALL is more efficient than UNION.

[RayMilhon]

never thought of using just Union. Tried it and it works either way. I'll keep what I originally posted because like you I think that the Union All is more efficient but I do get the same result either way.

[pbaldy]

The point was you would not get the same result dropping the DISTINCT:

SELECT dbo_tbl_HPCODEs.PRODUCTLINE
FROM dbo_tbl_HPCODEs
GROUP BY dbo_tbl_HPCODEs.PRODUCTLINE
UNION ALL
select "ACO" as PL
from dbo_tbl_hpcodes;