Sort Oder not as I planned "AkAlan via AccessMonster.com" <u18147[ at ]uwe> 11/24/2008 8:36:48 PM I have a view created with a sort order on the SiteName field but it sorts in the SiteID field. Am I missing simething here? SELECT DISTINCT TOP 100 PERCENT dbo.QcCar.SiteID, dbo.QcSites.SiteName FROM dbo.QcSites RIGHT OUTER JOIN dbo.QcCar ON dbo.QcSites.QcSitesId = dbo.QcCar.SiteID WHERE (dbo.QcCar.QcFinalReviewDate IS NULL) ORDER BY dbo.QcSites.SiteName -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200811/1

Re: Sort Oder not as I planned "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 11/24/2008 9:00:22 PM There are no sorting order in Views because they are interpreted like tables. Under some circonstances, adding a TOP 100 PERCENT will give you the impression that this work but as you might now see, this doesn't work 100% of the time on all platforms/applications. Use a Select statement to query your view and add your Order By to it. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "AkAlan via AccessMonster.com" <u18147[ at ]uwe> wrote in message news:8daee91c7a5b5[ at ]uwe... [Quoted Text] >I have a view created with a sort order on the SiteName field but it sorts >in > the SiteID field. Am I missing simething here? > > SELECT DISTINCT TOP 100 PERCENT dbo.QcCar.SiteID, dbo.QcSites.SiteName > FROM dbo.QcSites RIGHT OUTER JOIN > dbo.QcCar ON dbo.QcSites.QcSitesId = > dbo.QcCar.SiteID > WHERE (dbo.QcCar.QcFinalReviewDate IS NULL) > ORDER BY dbo.QcSites.SiteName > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200811/1 >

Re: Sort Oder not as I planned "AkAlan via AccessMonster.com" <u18147[ at ]uwe> 11/24/2008 9:15:00 PM Perfect, Thanks Sylvain Lafontaine wrote: [Quoted Text] >There are no sorting order in Views because they are interpreted like >tables. Under some circonstances, adding a TOP 100 PERCENT will give you >the impression that this work but as you might now see, this doesn't work >100% of the time on all platforms/applications. > >Use a Select statement to query your view and add your Order By to it. > >>I have a view created with a sort order on the SiteName field but it sorts >>in >[quoted text clipped - 6 lines] >> WHERE (dbo.QcCar.QcFinalReviewDate IS NULL) >> ORDER BY dbo.QcSites.SiteName -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200811/1

Re: Sort Oder not as I planned "asus" <xinxin99friend[ at ]163.com> 11/25/2008 11:51:38 AM "AkAlan via AccessMonster.com" <u18147[ at ]uwe> wrote in message news:8daee91c7a5b5[ at ]uwe... [Quoted Text] >I have a view created with a sort order on the SiteName field but it sorts >in > the SiteID field. Am I missing simething here? > > SELECT DISTINCT TOP 100 PERCENT dbo.QcCar.SiteID, dbo.QcSites.SiteName > FROM dbo.QcSites RIGHT OUTER JOIN > dbo.QcCar ON dbo.QcSites.QcSitesId = > dbo.QcCar.SiteID > WHERE (dbo.QcCar.QcFinalReviewDate IS NULL) > ORDER BY dbo.QcSites.SiteName > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200811/1 >

Re: Sort Order not as I planned Bob Snedden 12/10/2008 7:33:01 PM Dear Sylvain, I have an important customer who has upgraded to SQL Server 2005 with 9 ADP databases I have designed for them. This upgrade was done for replication purposes for other SQL Server applications not my databases. From you reply to this post - Am I now having to update all comboboxes to use store procedures or to use SELECT statements from Views in order to get sorted lists? This looks like a mountain of work but I assume because 2005 treats Views as Tables unlike 200. -- Bob Snedden, MCT, MCSD.Net "Sylvain Lafontaine" wrote: [Quoted Text] > There are no sorting order in Views because they are interpreted like > tables. Under some circonstances, adding a TOP 100 PERCENT will give you > the impression that this work but as you might now see, this doesn't work > 100% of the time on all platforms/applications. > > Use a Select statement to query your view and add your Order By to it. > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: sylvain aei ca (fill the blanks, no spam please) > > > "AkAlan via AccessMonster.com" <u18147[ at ]uwe> wrote in message > news:8daee91c7a5b5[ at ]uwe... > >I have a view created with a sort order on the SiteName field but it sorts > >in > > the SiteID field. Am I missing simething here? > > > > SELECT DISTINCT TOP 100 PERCENT dbo.QcCar.SiteID, dbo.QcSites.SiteName > > FROM dbo.QcSites RIGHT OUTER JOIN > > dbo.QcCar ON dbo.QcSites.QcSitesId = > > dbo.QcCar.SiteID > > WHERE (dbo.QcCar.QcFinalReviewDate IS NULL) > > ORDER BY dbo.QcSites.SiteName > > > > -- > > Message posted via AccessMonster.com > > http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200811/1 > > > > >

Re: Sort Order not as I planned "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 12/10/2008 7:49:15 PM You can try replacing the TOP 100% in the Views with something like TOP 2000000000. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Bob Snedden" <bob[ at ]discussions.microsoft.com> wrote in message news:06DE3D78-CE04-4FBA-A180-458313B6823F[ at ]microsoft.com... [Quoted Text] > Dear Sylvain, > > I have an important customer who has upgraded to SQL Server 2005 with 9 > ADP > databases I have designed for them. > > This upgrade was done for replication purposes for other SQL Server > applications not my databases. > > From you reply to this post - Am I now having to update all comboboxes to > use store procedures or to use SELECT statements from Views in order to > get > sorted lists? > > This looks like a mountain of work but I assume because 2005 treats Views > as > Tables unlike 200. > -- > Bob Snedden, MCT, MCSD.Net > > > "Sylvain Lafontaine" wrote: > >> There are no sorting order in Views because they are interpreted like >> tables. Under some circonstances, adding a TOP 100 PERCENT will give you >> the impression that this work but as you might now see, this doesn't work >> 100% of the time on all platforms/applications. >> >> Use a Select statement to query your view and add your Order By to it. >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: sylvain aei ca (fill the blanks, no spam please) >> >> >> "AkAlan via AccessMonster.com" <u18147[ at ]uwe> wrote in message >> news:8daee91c7a5b5[ at ]uwe... >> >I have a view created with a sort order on the SiteName field but it >> >sorts >> >in >> > the SiteID field. Am I missing simething here? >> > >> > SELECT DISTINCT TOP 100 PERCENT dbo.QcCar.SiteID, dbo.QcSites.SiteName >> > FROM dbo.QcSites RIGHT OUTER JOIN >> > dbo.QcCar ON dbo.QcSites.QcSitesId = >> > dbo.QcCar.SiteID >> > WHERE (dbo.QcCar.QcFinalReviewDate IS NULL) >> > ORDER BY dbo.QcSites.SiteName >> > >> > -- >> > Message posted via AccessMonster.com >> > http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200811/1 >> > >> >> >>