Posted by Beowulf on 01/24/06 18:19

I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don't come through
sorted by Numb.

Everything I've read on the web suggests that including the TOP
directive should enable ORDERY BY in views. Does someone have an idea
why the sorting is not working correctly for this particular view? thanks.

CREATE VIEW vwRouteReference
AS
SELECT TOP 100 PERCENT 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 TOP 100 PERCENT 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 tblItem.Numb

[Back to original message]