convert access crosstab to sqlview?

[Maureen]

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

 

[Duane Hookom]

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.

 

[Maureen]

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

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

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

 

[Duane Hookom]

"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

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

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

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","

Most
are

 

[Gary Walter]

Hi Maureen,

I don't know if this will mean much, but...

when I first started, I wrote out a little
cheat sheet for myself. Some may be
trivial, and there are probably many more,
but it helped me.


1) No TRIM
LTRIM(RTRIM([field])
2) No MID
SUBSTRING([field], start, length)
3) No Nz nor IIF(IsNull(a),b)

COALESCE(a,b)
// returns first nonNULL expression among its arguments;
// any number of arguments;
// if all NULL, returns NULL

CASE equivalent of COALESCE:

CASE
WHEN (arg1 IS NOT NULL)
THEN arg1
........................
WHEN (argn IS NOT NULL)
THEN argn
ELSE NULL
END

ISNULL(a,0.0) //if a is null, returns 0.0

NULLIF(arg1, arg2)
// returns null (type of first argument) if its 2 arguments are equal
// returns first argument if they are not equal


COALESCE(NULLIF(val2000,0), NULLIF(val2001,0), NULLIF(val2003,0))
if val2000 = 0, coalesce moves on to next expression

4) do not concatenate with "&"

use "+"

5) LEN() ignores trailing blanks

use DATALENGTH()

6) No CStr

CONVERT(char(12), GETDATE(), 3)
// style 3 = dd/mm/yy
// style 103 = dd/mm/yyyy (British/French)
// style 102 = yyyy.mm.dd (ANSI)
// style 1 = mm/dd/yy (USA)
// style 101 = mm/dd/yyyy (USA)
// style 110 = mm-dd-yyyy (USA)
// style 8 = hh:mm:ss
// style 100 = mon dd yyyy hh:mi AM or PM (default)
// style 109 = mon dd yyyy hh:mi:ss:mmm AM or PM (default + millisecs)
// style 107 = Mon dd, yyyy
// style 120 = yyyy-mm-dd hh:mi:ss (24hr) (ODBC canonical)

CAST([field] AS Char(50))

7) DATES

delimiter = single quote (not "#")
// WHERE OrderDate = 'Sep 13 1996'
// WHERE Dte = '20021221'
// WHERE PostDate = CONVERT(DATETIME, '2003-08-18 00:00:00', 102)

8) LIKE '%abc%' must use single quotes

9) No FORMAT

CONVERT(datatype, [length], expression, [style])

STR(float_expression, [length, decimal]]) //when exp>len, returns **
//length
(default=10) must be >=
//digits
of expr before decimal pt

//STR(123.45,6,1) = <space>123.5
//STR(12,4)
= <space><space>12

REPLACE(STR(@Num, 11, 2), SPACE(1), '0') // pad float with zeroes on
left

10) CASE WHEN
//when designing a View, insert CASE WHEN's last
// because designer cannot graphically represent them
//so from then on you must work only with SQL

 

[Duane Hookom]

Great stuff Gary!

 

[Maureen]

Gary,

Thanks sooo much! I knew of very few of these - this will be helpful as I
move ahead! I have only just started working with our new sql back end
discovering issues as I go. I recall that I had one query with criteria of
between 1 and 10 that when run against an access back end worked fine. When
I ran it against a sql back end I had to reverse it to between 10 and 1.

I may actually break down and go buy a book

M

 

[Maureen]

Duane,

The few queries that I have changed to views are incredible, one that took
20 minutes to run against the old access back end redone as a view now takes
less than a minute even with all the users (only 12) plugging away and the
log file backup running. We have lots of horsepower on the server.

Thanks for your help and info! I will do some skulking in the sql groups and
may actually break down and buy a book.

M

"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

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

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


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","