Help: Convert these Access queries to Microsoft SQL 2000 queries:

  • Thread starter Thread starter admlangford
  • Start date Start date
A

admlangford

Hi, can someone please help me convert the two Access queries below to
MSSQL 2000 queries?

Note; there are only ever three categories 1,2 and 3
============================
Query 1)
TRANSFORM Sum(IIf([RESULT]="FINISHED",1,0)) AS Expr2
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];

Query 2)
TRANSFORM Count(TABLEB.RESULT) AS RESULTCOUNT
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY) In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
============================

Thank you for any and all help
Adam
 
Hi, can someone please help me convert the two Access queries below to
MSSQL 2000 queries?

Note; there are only ever three categories 1,2 and 3
============================
Query 1)
TRANSFORM Sum(IIf([RESULT]="FINISHED",1,0)) AS Expr2
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];

Query 2)
TRANSFORM Count(TABLEB.RESULT) AS RESULTCOUNT
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY) In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
============================

Thank you for any and all help
Adam
Click to expand...

select TABLEA.ATTRIBUTE1
,sum(case when Category = 1 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_1]
,sum(case when Category = 2 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_2]
,sum(case when Category = 3 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_3]from
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1

query 2 you can make it similar to this :)
 
Hi, can someone please help me convert the two Access queries below to
MSSQL 2000 queries?

Note; there are only ever three categories 1,2 and 3
============================
Query 1)
TRANSFORM Sum(IIf([RESULT]="FINISHED",1,0)) AS Expr2
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];

Query 2)
TRANSFORM Count(TABLEB.RESULT) AS RESULTCOUNT
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY) In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
============================

Thank you for any and all help
Adam
Click to expand...

These look like queries that implement cross-tab reports. What
reporting/presentation solution are you using for SQL Server? Most of
those tools (Reporting Services for example) will implement cross-tabs
without the need to write such queries.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
 
Hi, can someone please help me convert the two Access queries below to
MSSQL 2000 queries?
Click to expand...
Note; there are only ever three categories 1,2 and 3
============================
Query 1)
TRANSFORM Sum(IIf([RESULT]="FINISHED",1,0)) AS Expr2
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
Click to expand...
Query 2)
TRANSFORM Count(TABLEB.RESULT) AS RESULTCOUNT
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY) In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
============================
Click to expand...
Thank you for any and all help
Adam
Click to expand...

These look like queries that implement cross-tab reports. What
reporting/presentation solution are you using for SQL Server? Most of
those tools (Reporting Services for example) will implement cross-tabs
without the need to write such queries.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--- Hide quoted text -

- Show quoted text -
Click to expand...

Hi, we are using Reporting Services MSSQL 2000.
Thanks
Adam
 
Hi, can someone please help me convert the two Access queries below to
MSSQL 2000 queries?
Click to expand...
Note; there are only ever three categories 1,2 and 3
============================
Query 1)
TRANSFORM Sum(IIf([RESULT]="FINISHED",1,0)) AS Expr2
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
Click to expand...
Query 2)
TRANSFORM Count(TABLEB.RESULT) AS RESULTCOUNT
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY) In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
============================
Click to expand...
Thank you for any and all help
Adam
Click to expand...

select TABLEA.ATTRIBUTE1
,sum(case when Category = 1 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_1]
,sum(case when Category = 2 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_2]
,sum(case when Category = 3 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_3]from
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1

query 2 you can make it similar to this :)- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks :) That worked perfectly
 
Back
Top