Migrate MS-Access to SQL-Server - iif construction in aggregation query failes 
Author Message
 Migrate MS-Access to SQL-Server - iif construction in aggregation query failes

Dear Collegues,

In ms-access code below works fine: depending on value of IsActive it
summarizes in ActiveSum either InActiveSum

SELECT Sum(IIf([IsActive],[TimeActual],0)) AS ActiveSum, Sum(IIf(Not
[IsActive],[TimeActual],0)) AS InActiveSum, Delivery.CompanyID,
Delivery.PersonID, Delivery.IsActive
FROM Delivery
GROUP BY Delivery.CompanyID, Delivery.PersonID, Delivery.IsActive;

I want to do the same in transact-SQL with one single SQL statement.

Who knows how to do it in transact-SQL?????????

Are there any source where I can find more about migrating access queries to
transact-SQL queries?

Thanks for your answer,
Alexis



Sun, 19 Sep 2004 23:54:13 GMT
 Migrate MS-Access to SQL-Server - iif construction in aggregation query failes

Substitute IIF by CASE statements in SQL Server !

HTH

--

*****************************************************************



Quote:
> Dear Collegues,

> In ms-access code below works fine: depending on value of IsActive it
> summarizes in ActiveSum either InActiveSum

> SELECT Sum(IIf([IsActive],[TimeActual],0)) AS ActiveSum, Sum(IIf(Not
> [IsActive],[TimeActual],0)) AS InActiveSum, Delivery.CompanyID,
> Delivery.PersonID, Delivery.IsActive
> FROM Delivery
> GROUP BY Delivery.CompanyID, Delivery.PersonID, Delivery.IsActive;

> I want to do the same in transact-SQL with one single SQL statement.

> Who knows how to do it in transact-SQL?????????

> Are there any source where I can find more about migrating access queries
to
> transact-SQL queries?

> Thanks for your answer,
> Alexis



Sun, 19 Sep 2004 17:27:27 GMT
 Migrate MS-Access to SQL-Server - iif construction in aggregation query failes
You should be able to use the CASE statement in transact SQL to do what
you're doing in Access. It would look something like:

select sum(case IsActive when 0 then 0 else TimeActual end) as
ActiveSum,
   sum(case IsActive when 0 then TimeActual else 0 end) as InActiveSum,
   Delivery.CompanyID,
   Delivery.PersonID,
   Delivery.IsActive
from Delivery
group by Delivery.CompanyID, Delivery.PersonID, Delivery.IsActive

I have not tested this, so there may be typos, etc.

For more information on converting Access to SQL I would go to
www.microsoft.com and do a search on "access sql conversion" and see
what comes up. I'm sure that they have a whitepaper or something on it.

   Good luck,
      -Tom.


<x> says...

Quote:
> Dear Collegues,

> In ms-access code below works fine: depending on value of IsActive it
> summarizes in ActiveSum either InActiveSum

> SELECT Sum(IIf([IsActive],[TimeActual],0)) AS ActiveSum, Sum(IIf(Not
> [IsActive],[TimeActual],0)) AS InActiveSum, Delivery.CompanyID,
> Delivery.PersonID, Delivery.IsActive
> FROM Delivery
> GROUP BY Delivery.CompanyID, Delivery.PersonID, Delivery.IsActive;

> I want to do the same in transact-SQL with one single SQL statement.

> Who knows how to do it in transact-SQL?????????

> Are there any source where I can find more about migrating access queries to
> transact-SQL queries?

> Thanks for your answer,
> Alexis



Mon, 20 Sep 2004 00:33:12 GMT
 Migrate MS-Access to SQL-Server - iif construction in aggregation query failes
You use a CASE expression.
See http://www.aspfaq.com/show.asp?id=2214 for an example.



Quote:
> Dear Collegues,

> In ms-access code below works fine: depending on value of IsActive it
> summarizes in ActiveSum either InActiveSum

> SELECT Sum(IIf([IsActive],[TimeActual],0)) AS ActiveSum, Sum(IIf(Not
> [IsActive],[TimeActual],0)) AS InActiveSum, Delivery.CompanyID,
> Delivery.PersonID, Delivery.IsActive
> FROM Delivery
> GROUP BY Delivery.CompanyID, Delivery.PersonID, Delivery.IsActive;

> I want to do the same in transact-SQL with one single SQL statement.

> Who knows how to do it in transact-SQL?????????

> Are there any source where I can find more about migrating access queries
to
> transact-SQL queries?

> Thanks for your answer,
> Alexis



Mon, 20 Sep 2004 00:36:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Migrate MS Access Query To SQL Server 7

2. How to migrate from MS Access 97 to MS SQL server 6.5

3. How to migrate from MS-ACCESS to MS-SQL server 6.5/7.0

4. Problems with sql queries after migrating from access to sql server

5. migrate from MS Access to SQL Server 7.0

6. Migrating from Normal Access Database to MS SQL Server

7. Migrating queries in Access 2000 to SQL Server 2000

8. Migrating Queries from Access to SQL Server 7

9. Query doesn't work after migrating from Access to SQL Server 7

10. Query not working after migrating from Access to SQL Server 7

11. Converting MS Access Query to MS SQL Query

12. Converting MS Access Query to SQL server query


 
Powered by phpBB® Forum Software