Page 1 of 1
SQL Query
Posted: Thu Mar 08, 2007 9:08 am
by Gen
Code: Select all
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...
Posted: Thu Mar 08, 2007 11:40 am
by BeAViS
you didn't say the magic word...
Posted: Thu Mar 08, 2007 11:45 am
by Gen
Ooo.. sorry
PLEASE
abracadabra, ocus focus
Posted: Thu Mar 08, 2007 11:48 am
by Gen
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