SQL Statement

  • Thread starter Thread starter Cindy
  • Start date Start date
C

Cindy

From this SQL State, Could anybody tell me how to list my
products name in alphabetical? Order by?

select "*" as ProductID, "*All*" as Product from Product
UNION SELECT Product.ProductID, ( [ProductName]&" "&
[ProductDetail]) AS Name FROM ProductID;

Thank you,
Cindy
 
Cindy,

I had to play with this a little to get it to come back the way I think you
probably want it. Try the following. Note that I replaced your composite
field name [NAME] with Prod_Name, since [NAME] is a reserved word and should
not be used as a field name. I'm not sure why this would not work for me
when I put the SELECT Top statement first, think it had something to do with
the Order By clause.

SELECT tbl_Product.ProductID, [ProductName] & " " & [ProductDetail] AS
Prod_Name
FROM tbl_Product
ORDER BY [ProductName] & " " & [ProductDetail]
UNION
Select TOP 1 "*" as ProductID, "*ALL*" as Prod_Name FROM tbl_Product

HTH
Dale

BTW, if your ProductID field is numeric, this will not work because the
datatype of each field in the Union must match the datatype of the matching
fields in the other part(s) of the union.
 
Cindy said:
From this SQL State, Could anybody tell me how to list my
products name in alphabetical? Order by?

select "*" as ProductID, "*All*" as Product from Product
UNION SELECT Product.ProductID, ( [ProductName]&" "&
[ProductDetail]) AS Name FROM ProductID;
Click to expand...

You only need to add an Order By clause.

Note:
You seem to have some kind of mixup in the table/field
names which I took a shot at changing.

If possible, it is much better to use UNION ALL instead of
just UNION. To eliminate the duplicate rows in the first
select statement, create a table with one row of arbitrary
data:

SELECT "*" As ProductID, "*All*" As Descr
FROM OneRow
UNION
SELECT ProductID,
([ProductName] & " " & [ProductDetail])
FROM Product
ORDER BY Descr


Note:
If possible, it is much better to use UNION ALL instead of
just UNION. To eliminate the duplicate rows in the first
select statement, create a table with one row of arbitrary
data
 
Back
Top