Access to SQL server 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Access to SQL server 2005

Author

Topic

mike009
Starting Member

15 Posts

Posted - 2009-03-17 : 08:52:37

I have the following query;


TRANSFORM Sum(qry_RESOURCES_VALUES_MONTH_SOM_1.VALUE_SOM) AS SumOfVALUE_Som
SELECT qry_RESOURCES_VALUES_MONTH_SOM_1.PROJECT_ID, 
qry_RESOURCES_VALUES_MONTH_SOM_1.RESSOURCE_ID, 
qry_RESOURCES_VALUES_MONTH_SOM_1.PROFIL_ID, 
qry_RESOURCES_VALUES_MONTH_SOM_1.DATE_YEAR, 
Sum(qry_RESOURCES_VALUES_MONTH_SOM_1.VALUE_SOM) AS [Total Of VALUE_Som]
FROM qry_RESOURCES_VALUES_MONTH_SOM_1
GROUP BY qry_RESOURCES_VALUES_MONTH_SOM_1.PROJECT_ID,
qry_RESOURCES_VALUES_MONTH_SOM_1.RESSOURCE_ID, 
qry_RESOURCES_VALUES_MONTH_SOM_1.PROFIL_ID, qry_RESOURCES_VALUES_MONTH_SOM_1.DATE_YEAR
PIVOT qry_RESOURCES_VALUES_MONTH_SOM_1.DATE_MONTH;

I want to convert it to sql server 2005 query , what i did is the following


SELECT     PROJECT_ID, RESSOURCE_ID, PROFIL_ID, DATE_YEAR,
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 1) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '1',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 2) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '2',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 3) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '3',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 4) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '4',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 5) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '5',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 6) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '6',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 7) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '7',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 8) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '8',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 9) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '9',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 10) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '10',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 11) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '11',
                          (SELECT     SUM(VALUE_SOM) AS Expr1
                            FROM          dbo.qry_RESOURCES_VALUES_MONTH_SOM_1
                            WHERE      (DATE_MONTH = 12) AND (PROJECT_ID = VTE.PROJECT_ID) AND (RESSOURCE_ID = VTE.RESSOURCE_ID) AND 
                                                   (PROFIL_ID = VTE.PROFIL_ID) AND (DATE_YEAR = VTE.DATE_YEAR)) AS '12'
FROM         dbo.qry_RESOURCES_VALUES_MONTH_SOM_1 AS VTE
GROUP BY PROJECT_ID, RESSOURCE_ID, PROFIL_ID, DATE_YEAR

But some values are not correct and i also don't know how to sum one row in a new colloumn (in access the column name is "SumOfVALUE_Som")

Thank you in advance
Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources