SQL Query

Gen · Post by **Gen** » Thu Mar 08, 2007 9:08 am

TRANSFORM Sum(RRHH!Hours) AS [Hours by Year]
SELECT RRHH.NumCaso, Proyects.NameProy, Peopole.Lastnames, Peopole.Name, RRHH.Cargo, Sum(RRHH!ValueHoursMan*RRHH!Hours) AS Total
FROM Proyects INNER JOIN (Peopole INNER JOIN RRHH ON Peopole.IdPeopole=RRHH.IdPeopole) ON Proyects.NumCaso=RRHH.NumCaso
WHERE (((Year(RRHH!Date))=2007))
GROUP BY RRHH.NumCaso, Proyects.NameProy, Peopole.Lastnames, Peopole.Name, RRHH.Cargo
PIVOT Month(RRHH!Date);

this is the result
NumCaso, NameProy, Lastnames, Name, Cargo, Total, jan, feb.....

hours month by month, but I need the amount of money month by month, I mean for January Sum(RRHH!ValueHoursMan*RRHH!Hours) on January, the same for feb etc...

BeAViS · Post by **BeAViS** » Thu Mar 08, 2007 11:40 am

you didn't say the magic word...

Gen · Post by **Gen** » Thu Mar 08, 2007 11:45 am

Ooo.. sorry

PLEASE

abracadabra, ocus focus

Gen · Post by **Gen** » Thu Mar 08, 2007 11:48 am

Code: Select all

TRANSFORM Sum(RRHH!Hours*RRHH!ValueHoursMan) AS [Total]
SELECT RRHH.NumCaso, Proyects.NameProy, Peopole.Lastnames, Peopole.Name, RRHH.Cargo
FROM Proyects INNER JOIN (Peopole INNER JOIN RRHH ON Peopole.IdPeopole=RRHH.IdPeopole) ON Proyects.NumCaso=RRHH.NumCaso
WHERE (((Year(RRHH!Date))=2007))
GROUP BY RRHH.NumCaso, Proyects.NameProy, Peopole.Lastnames, Peopole.Name, RRHH.Cargo
PIVOT Month(RRHH!Date);

I found the solution by my self, if some one have another solution or fix for my way to do it please help me