Sql 2005 with Access 2003 sort order issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fredrick A. Zilz - 28 Feb 2006 17:26 GMT

I have an access 2003 adp using a SQL 2005 database as the datasource. I
have a list which uses a function as the data source. When I execute the
function the results are sorted per the order by fields, but when I look at
the list in Access the list is seemingly random? Any ideas as to why?

SQL Function:
ALTER FUNCTION [dbo].[QtyByOrderNo_PendingOrders]()

RETURNS TABLE

AS

RETURN ( SELECT TOP 100 PERCENT CustomerOrders.IDCUST,
CustomerOrders.NAMECUST, dbo.Tbl_IH_LotNo.LotID, dbo.Tbl_IH_LotNo.MaLotNo,

dbo.Tbl_Inventory.OrderID, dbo.Tbl_IH_LotNo.OrderCode,
SUM(dbo.Tbl_Inventory.Qty) AS QtyOrdered, dbo.Tbl_Inventory.ORDUNIQ,

dbo.Tbl_Inventory.LINENUM, CustomerOrders.ORDDATE, CustomerOrders.EXPDATE,
CustomerOrders.ORIGQTY

FROM dbo.Tbl_Drums INNER JOIN

dbo.Tbl_SubLots ON dbo.Tbl_Drums.SubLotID = dbo.Tbl_SubLots.SubLotID INNER
JOIN

dbo.Tbl_IH_LotNo ON dbo.Tbl_SubLots.LotID = dbo.Tbl_IH_LotNo.LotID INNER
JOIN

dbo.Tbl_Inventory ON dbo.Tbl_Drums.DrumID = dbo.Tbl_Inventory.DrumID INNER
JOIN

dbo.CustomerOrders() CustomerOrders ON dbo.Tbl_Inventory.ORDUNIQ =
CustomerOrders.ORDUNIQ AND

dbo.Tbl_Inventory.LINENUM = CustomerOrders.LINENUM

WHERE (dbo.Tbl_Inventory.[Shipped Date] IS NULL)

GROUP BY dbo.Tbl_IH_LotNo.LotID, dbo.Tbl_IH_LotNo.MaLotNo,
dbo.Tbl_IH_LotNo.OrderCode, dbo.Tbl_Inventory.ORDUNIQ,
dbo.Tbl_Inventory.LINENUM,

dbo.Tbl_Inventory.OrderID, CustomerOrders.NAMECUST, CustomerOrders.IDCUST,
CustomerOrders.ORDDATE, CustomerOrders.EXPDATE,

CustomerOrders.ORIGQTY

HAVING (NOT (dbo.Tbl_Inventory.LINENUM IS NULL))

ORDER BY CustomerOrders.NAMECUST, dbo.Tbl_Inventory.OrderID )

What would make the Access 2003 list sort differently than the datasource?

Reply to this Message

Sylvain Lafontaine - 28 Feb 2006 18:49 GMT

The key word here is probably 2005.

It's a common misconception that Order By in Functions and Views should be
followed by SQL-Server; especially when you add a TOP 100 PERCENT clause.

The TOP clause is usually used in conjonction with an Order By to retrieve a
particular subset; however when you set it to 100%, it's pretty useless and
SQL-Server 2005 - beeing more optimized than its 2000 version counterpart -
simply drop it.

This is not an ADP problem here but a difference between SQL2000 and 2005;
the 2005 version beeing more in line with the published standard for the SQL
language.

You will have to enclose your call to the function inside a Select statement
with an Order By clause (the Order By clause on the Select statement and not
inside the function (or a view)).

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

>I have an access 2003 adp using a SQL 2005 database as the datasource. I
>have a list which uses a function as the data source. When I execute the
[quoted text clipped - 49 lines]
>
> What would make the Access 2003 list sort differently than the datasource?

Reply to this Message

Fredrick A. Zilz - 28 Feb 2006 20:57 GMT

Thank you, Thank you.
You resolved my issue and increased my SQL knowledge.

> The key word here is probably 2005.
>
[quoted text clipped - 69 lines]
>> What would make the Access 2003 list sort differently than the
>> datasource?

Reply to this Message