Posted by Doug on 10/01/48 11:38

you guys all missed it.

his first example is two ordered tables that are then unioned. In his
business problem, the final result is not ordered.

In his second example, he is ordering a table.

In order to solve his businss problem, the solution is:

CREATE VIEW vwRouteReference
AS
select * from
(
SELECT tblItem.ID,
tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,
tblItem.Numb, tblQuestion.DescrPrimary AS Type
FROM tblItem INNER JOIN tblQuestion
ON (tblItem.ID = tblQuestion.Item_ID)
WHERE (((tblItem.Category_ID)>0))
UNION
SELECT tblItem.ID,
tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS
FullName,
tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS Type
FROM tblItem
WHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR
(tblItem.Type) = 'Route'))
)
ORDER BY .Numb

This is the brute force way.
Fundamentally, you are unioning two data sets, then ordering them, then
returning them.

[Back to original message]