Hi All

I need some help to rewrite Access SQL tol SQL Express

TRANSFORM Count(tbl_ORDH_STAT.RECID) AS AntalOfRECID
SELECT tbl_ORDH_STAT.O_UGE
FROM tbl_ORDH_STAT
WHERE (((tbl_ORDH_STAT.O_UGE) Between "1101" And "1120") AND
((tbl_ORDH_STAT.TYPE)<>"REST") AND ((tbl_ORDH_STAT.Fab_1)=True))
GROUP BY tbl_ORDH_STAT.O_UGE
PIVOT tbl_ORDH_STAT.TYPE;

Thanks in advance

//MOL

olesen wrote:
If possible, you can create a linked server in SQL Server that points at
your Access database. Change tbl_ORDH_STAT to a linked table that points at
the table in SQL Express, and you can run this query as-written from SQL
Express.

If that's not a possibility, you will need to rewrite the query of course.
You will need to know the possible values for TYPE, unless you plan on using
dynamic sql. The idea is, assuming possible values for TYPE are "A' and "B":

SELECT O_UGE
, SUM(CASE [TYPE] WHEN 'A' THEN 1 ELSE 0 END) [A]
, SUM(CASE [TYPE] WHEN 'B' THEN 1 ELSE 0 END) [b]
, SUM(CASE WHEN [TYPE] NOT IN ('A','B') THEN 1 ELSE 0 END)
[Other] --optional
FROM tbl_ORDH_STAT
WHERE O_UGE Between '1101' And '1120' AND Fab_1 = 1
GROUP BY O_UGE

Substitute the actual values from your table and add additional case
statements as needed.
If you will not know the possible TYPE values until runtime then you will
need to use dynamic sql to build a statement that looks like the above. See
Erland Sommarskog's article on dynamic sql for help with that:
http://www.sommarskog.se/dynamic_sql.html

I don't do this very often, but you might try a T-SQL statement like the
following if your version of SQL Express is 2005 or later.

SELECT O_UGE
FROM
(SELECT O_UGE, RecID
FROM tbl_ORDH_Stat
WHERE O_UGE Between '1101' and '1120'
AND Type <> 'Rest' and Fab_1 <> 0) as Temp
PIVOT Count(RecID)

I always have to play around to get the syntax correct for a Pivot query in T-SQL.


John Spencer
Access MVP 2002-2005, 2007-2011

On 6/28/2011 2:48 AM, olesen wrote:

John Spencer wrote:

Actually, that's why I still avoid that syntax. There are really no benefits
to using the new PIVOT operator, and there are actually some downsides. So I
still tend to use CASE statements with GROUP BY.