Sort in UNION query

[Guest]

Thanks for taking the time to read my question.

I want to sort the data returned so that "All" and "Blank" show up at the
top. I can't get it to do that.

tblCostMaster.CFPROD returns the values, and the Type field will have
nothing in it (Col 1) and has the values of CFPROD in column 2. The first and
second SELECT statements return "Blank" and "All" in the first column, and 0,
and -1 in the second column.

For some reason I can't sort on the first column Type, and get the "Blank"
and "All" values to appear at the top of the returned data. They sort, but
stay at the bottom of the data.

Not sure why.

Thanks for your help,

Brad

Code
===============
SELECT "Blank" as Type, 0 as Junk
FROM tblCostMaster
ORDER BY "Blank" DESC

UNION SELECT "All" as Type, -1 as Junk
FROM tblCostMaster

UNION SELECT "" AS Type, tblCostMaster.CFPROD
FROM tblCostMaster
GROUP BY "", tblCostMaster.CFPROD
HAVING ((Not (tblCostMaster.CFPROD) Is Null));
=================

 

[John Spencer]

In a UNION query the ORDER BY statement uses the field names from the first
Query clause, but it goes at the end of the last query clause

===============
SELECT "Blank" as [Type], 0 as Junk
FROM tblCostMaster

UNION SELECT "All" as [Type], -1 as Junk
FROM tblCostMaster

UNION SELECT "" AS [Type], tblCostMaster.CFPROD
FROM tblCostMaster
GROUP BY "", tblCostMaster.CFPROD
HAVING ((Not (tblCostMaster.CFPROD) Is Null))
ORDER BY [Type] DESC
=================

Be careful, Type is a reserved word in Access. That is why I chose to
surround it with brackets [].

 

[Guest]

Thanks for the help! And for the heads up on TYPE.

I'll change that.

Have a great day,

Brad

In a UNION query the ORDER BY statement uses the field names from the first
Query clause, but it goes at the end of the last query clause

===============
SELECT "Blank" as [Type], 0 as Junk
FROM tblCostMaster

UNION SELECT "All" as [Type], -1 as Junk
FROM tblCostMaster

UNION SELECT "" AS [Type], tblCostMaster.CFPROD
FROM tblCostMaster
GROUP BY "", tblCostMaster.CFPROD
HAVING ((Not (tblCostMaster.CFPROD) Is Null))
ORDER BY [Type] DESC
=================

Be careful, Type is a reserved word in Access. That is why I chose to
surround it with brackets [].

Thanks for taking the time to read my question.

I want to sort the data returned so that "All" and "Blank" show up at the
top. I can't get it to do that.

tblCostMaster.CFPROD returns the values, and the Type field will have
nothing in it (Col 1) and has the values of CFPROD in column 2. The first
and
second SELECT statements return "Blank" and "All" in the first column, and
0,
and -1 in the second column.

For some reason I can't sort on the first column Type, and get the "Blank"
and "All" values to appear at the top of the returned data. They sort,
but
stay at the bottom of the data.

Not sure why.

Thanks for your help,

Brad

Code
===============
SELECT "Blank" as Type, 0 as Junk
FROM tblCostMaster
ORDER BY "Blank" DESC

UNION SELECT "All" as Type, -1 as Junk
FROM tblCostMaster

UNION SELECT "" AS Type, tblCostMaster.CFPROD
FROM tblCostMaster
GROUP BY "", tblCostMaster.CFPROD
HAVING ((Not (tblCostMaster.CFPROD) Is Null));
=================