Re: convert access crosstab to sqlview?
From: Duane Hookom (duanehookom_at_NO_SPAMhotmail.com)
Date: 04/19/04
- Next message: Leonard Poon: "replacing of semi-joins"
- Previous message: Duane Hookom: "Re: count occurances in an option group"
- In reply to: Maureen: "Re: convert access crosstab to sqlview?"
- Next in thread: Maureen: "Re: convert access crosstab to sqlview?"
- Reply: Maureen: "Re: convert access crosstab to sqlview?"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 18 Apr 2004 23:57:50 -0500
"run faster" is related to total number of records, users, network,...
Just understand that your query uses several issues when converted to SQL
Server. Some are already mentioned such as SQL Server doesn't support
crosstabs. I also question the use of:
-"!"
-Nz() which can be replaced by Coalesce()
-- Duane Hookom MS Access MVP "Maureen" <nosend2me@hotmail.com> wrote in message news:uPO4crbJEHA.2556@TK2MSFTNGP11.phx.gbl... > Duane, > > Thanks for the note, I know very little about sql other than the fact views > run at 1000% faster than comparable queries. Will the code you suggested run > faster? I am not familiar with "CASE WHEN" I am trying to improve > performance on several reports that I have. rac2sql looks interesting and be > very helpful thanks for the suggestion. > > M > > "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message > news:ue7tbaZJEHA.232@TK2MSFTNGP12.phx.gbl... > > I assume you are referring to a SQL Server view which does not support > > TRANSFORM (until some future version). There is a third party utility that > > can be very helpful http://www.rac4sql.net/. > > > > You can also use the Sum( CASE WHEN DateDiff(w,FieldOne, GetDate()) = 0 > then > > Quantity ELSE 0) as Wk0 > > caution air code above. > > > > I would also take this opportunity to change all of your table and field > > names to remove those spaces. > > -- > > Duane Hookom > > MS Access MVP > > > > > > "Maureen" <nosend2me@hotmail.com> wrote in message > > news:eiyxMNZJEHA.1392@TK2MSFTNGP09.phx.gbl... > > > TRANSFORM Nz(Sum([Invoice Footer History]![Quantity]),0) AS Quantity > > > SELECT Inventory.MANUF, [Invoice Footer History].[Product Code], > > > Inventory.Description > > > FROM Inventory INNER JOIN ([Invoice Footer History] INNER JOIN [Invoice > > > Header History] ON [Invoice Footer History].[invoice number] = [Invoice > > > Header History].Invoice) ON Inventory.ProdCode = [Invoice Footer > > > History].[Product Code] > > > GROUP BY Inventory.MANUF, [Invoice Footer History].[Product Code], > > > Inventory.Description > > > ORDER BY Inventory.MANUF > > > PIVOT "Wk" & DateDiff("ww",[Invoice Date],Date()) In > > > > > > ("Wk0","Wk1","Wk2","Wk3","Wk4","Wk5","Wk6","Wk7","Wk8","Wk9","Wk10","Wk12"," > > > Wk13"); > > > > > > > > > I am converting a number of things from access qry's to sql views. Most > > are > > > working just fine with some minor tweaking. On this I am getting a > "syntax > > > error near SUM" any suggestions? > > > > > > M > > > > > > > > > > > >
- Next message: Leonard Poon: "replacing of semi-joins"
- Previous message: Duane Hookom: "Re: count occurances in an option group"
- In reply to: Maureen: "Re: convert access crosstab to sqlview?"
- Next in thread: Maureen: "Re: convert access crosstab to sqlview?"
- Reply: Maureen: "Re: convert access crosstab to sqlview?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
